SEEC Database: Revised Content, EER Diagram, Data Dictionary, Normalization Essay.

1. Revised content of assignment 1: Requirement Specification, EER Diagram and Data Dictionary, Transaction Requirements, Business Rules 
Requirements Specification (Clarity and completeness)
EER Diagram and Data Dictionary
Rightness & Completeness of EER model
Data dictionary and documentation

Transaction Requirements
Business Rules
Mapping of the EER model to the relational model in DBDL
Mapping of each relation & attributes
Mapping of each relationship

3. Normalizing the relational schema to Boyce-Codd Normal Form
Discussion of functional dependencies, judgement of what norm form that each relation is in, and process of normalising up to BCNF for all relations (e.g., if a relation is in 2nd normal form, give your reason why it is in 2nd form,then show all the process to normalise it up to BCNF).

At least 2 cases of normalisation are shown to demonstrate your understanding of normalisation (The exemplar could be from any norm form up to BCNF, e.g., from first norm form up to BCNF, or from third norm form up to BCNF. In case of not being able to identify lower norm form, some assumption of function dependency is acceptable).
 

Storing Student Data: The database should be able to store all the details of the students. The database must be able to uniquely identify student record.

Storing Staff Record: The database will only accept the staff details after staff is assigned to a department. The staff salary and other details must be discussed earlier. The database can record the resignation date of the staff.

Storing Loan Data: Each loan needs to be stored. This record will include who has taken the loan and which resource has been loaned. The loan records will store damages and fines.

Storing Resource Record: The resource data will be stored in a separate table. The resource table will be storing the manufacturer’s details too. 

Figure 1: Enhanced Entity Relationship Diagram 

Table: Student
Attribute	Description	Data Type	Data Length	Key	Reference Table
person_id	Uniquely identifies a student	int	11	Primary	None
name	Name of the student	varchar	100	None	None
gender	Whether student is male, female or other	varchar	100	None	None
age	Age of the student	int	11	None	None
date_of_birth	Student birth date	date		None	None
telephone_number	Contact number of student	varchar	100	None	None
address	Residential address of student	varchar	100	None	None
nationality	Nationality of the student	varchar	100	None	None
batch	To which batch the student belong to	year	4	None	None
course_name	Course name to which student has taken admission	varchar	100	None	None
parent	Guardian of the student	varchar	100	None	None
parent_contact_number	Contact number of guardian	varchar	100	None	None

Table: Staff
Attribute	Description	Data Type	Data Length	Key	Reference Table
person_id	Uniquely identifies a staff	int	11	Primary	None
name	Name of the staff	varchar	100	None	None
gender	Whether staff is male, female or other	varchar	100	None	None
age	Age of the staff	int	11	None	None
date_of_birth	Staff birth date	date		None	None
telephone_number	Contact number of staff	varchar	100	None	None
address	Residential address of staff	varchar	100	None	None
highest_qualification	The last qualification degree of the staff	varchar	100	None	None
join_date	The date on which staff started working for SEEC	date		None	None
leave_date	The date on which staff resigned from  SEEC	date		None	None
salary	Annual salary of the staff	decimal	12,2	None	None
department	Department name to which staff works in	varchar	100	None	None
contact_person	Person whom can be contacted in case of emergency	varchar	100	None	None
contact_number	Contact number of emergency contact person	varchar	100	None	None

Table: Resource
Attribute	Description	Data Type	Data Length	Key	Reference Table
resource_id	Uniquely identifies a resource	int	11	Primary	None
name	Name of the resource	varchar	100	None	None
type	The category to which resource belong to	varchar	100	None	None
manufacturer	The manufacturer of the resource such as company name	varchar	100	None	None
purchase_date	The date on which resource was purchased	date		None	None
purchase_price	The cost of the resource	decimal	12,2	None	None

Table: Loan
Attribute	Description	Data Type	Data Length	Key	Reference Table
loan_id	Uniquely identifies a staff	int	11	Primary	None
person_id	The person_id who has taken the loan	int	11	Foreign	Staff or Student
resource_id	The resource which has been loaned	int	11	Foreign	Resource
loan_date	The actual date on which loan was sanctioned	date		None	None
expected_return_date	The last date of return. Exceeding this date will lead to fine	date		None	None
actual_return_date	The return date of the	date		None	None
damage_details	The damages happened during loan	varchar	100	None	None
damage_condition	Condition of the damage such as minimal, low, medium, high, extreme	varchar	100	None	None
fine	Total fine charged due to late return and/or damage	decimal	12,2	None	None
due	Payment to be done for fine	decimal	12,2	None	None

The requirements of transactions are as following.

1. Student request for resource loan
2. Staff request for resource loan

• Student or staff returns the resource within expected date

1. Student or staff returns the resource after expected date
2. Student or staff returns the resource without damage
3. Student or staff returns the resource with damage

• Student admits into SEEC
• Staff joins SEEC

1. Staff resigns from SEEC

The business rules of SEEC database are as following.

1. A resource can be loaned once at a time
2. Student and staff have to give id card while requesting for loan

• A fine can be zero if the return date is within expected date and no damage is done to resource

1. A resource can be loaned either by staff or student

CREATE TABLE `loan` (

  `loan_id` int(11) NOT NULL,

  `person_id` int(11) NOT NULL,

  `resource_id` int(11) NOT NULL,

  `loan_date` date NOT NULL,

  `expected_return_date` date NOT NULL,

  `actual_return_date` date NOT NULL,

  `damage_details` varchar(100) NOT NULL,

  `damage_condition` varchar(100) NOT NULL,

  `fine` decimal(12,2) NOT NULL,

  `due` decimal(12,2) NOT NULL

);

CREATE TABLE `resource` (

  `resource_id` int(11) NOT NULL,

  `name` varchar(100) NOT NULL,

  `type` varchar(100) NOT NULL,

  `manufacturer` varchar(100) NOT NULL,

  `purchase_date` date NOT NULL,

  `purchase_price` decimal(12,2) NOT NULL

);

CREATE TABLE `staff` (

  `person_id` int(11) NOT NULL,

  `name` varchar(100) NOT NULL,

  `gender` varchar(100) NOT NULL,

  `age` int(11) NOT NULL,

  `date_of_birth` date NOT NULL,

  `telephone_number` varchar(100) NOT NULL,

  `address` varchar(100) NOT NULL,

  `nationality` varchar(100) NOT NULL,

  `highest_qualification` varchar(100) NOT NULL,

  `join_date` date NOT NULL,

  `leave_date` date NOT NULL,

  `salary` decimal(12,2) NOT NULL,

  `department` varchar(100) NOT NULL,

  `contact_person` varchar(100) NOT NULL,

  `contact_number` varchar(100) NOT NULL

);

CREATE TABLE `student` (

  `person_id` int(11) NOT NULL,

  `name` varchar(100) NOT NULL,

  `gender` varchar(100) NOT NULL,

  `age` int(11) NOT NULL,

  `date_of_birth` date NOT NULL,

  `telephone_number` varchar(100) NOT NULL,

  `address` varchar(100) NOT NULL,

  `nationality` varchar(100) NOT NULL,

  `batch` year(4) NOT NULL,

  `course_name` varchar(100) NOT NULL,

  `parent` varchar(100) NOT NULL,

  `parent_contact_number` varchar(100) NOT NULL

);

ALTER TABLE `loan`

  ADD PRIMARY KEY (`loan_id`),

  ADD KEY `resource_id` (`resource_id`),

  ADD KEY `person_id` (`person_id`);

ALTER TABLE `resource`

  ADD PRIMARY KEY (`resource_id`);

ALTER TABLE `staff`

  ADD PRIMARY KEY (`person_id`);

ALTER TABLE `student`

  ADD PRIMARY KEY (`person_id`);

ALTER TABLE `loan`

  ADD CONSTRAINT `loan_ibfk_1` FOREIGN KEY (`resource_id`) REFERENCES `resource` (`resource_id`),

  ADD CONSTRAINT `loan_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `staff` (`person_id`),

  ADD CONSTRAINT `loan_ibfk_3` FOREIGN KEY (`person_id`) REFERENCES `student` (`person_id`);

First Normal Form: The first normal form of database states that every data record needs to be unique and each column have to have atomic values. Therefore, the first normal form database will have student/staff, loan and resource attributes in one table. For every loan, the staff and resource records will be written individually. The first normal form database table will look like following.

student_id	Student Attributes	staff_id	Staff Attributes	loan_id	Loan attributes	Resource Attributes
Value1	Value1			Value1	Value1	Value1
Value1	Value1			Value2	Value2	Value2
		Value2	Value2	Value3	Value1	Value1
		Value2	Value2	Value4	Value2	Value2

Second Normal Form: Second normal form states that tables need to be partial dependency independence. Partial dependency presents in a database when a non-key attribute is dependent on another non-key attribute. This dependency can also be present if more than one primary key is present in the tables. In order to remove partial dependency, tables are divided into smaller tables. However, after division, the relation between the attributes are lost and to retain this relationship, the foreign keys are used. In the proposed database student and staff attributes are dependent on different primary keys. After removing the partial dependencies, the tables will look like following.

student_id	Student Attributes
Value1	Value1
Value1	Value1

staff_id	Staff Attributes
Value2	Value2
Value2	Value2

loan_id	person_id	Loan attributes	Resource Attributes
Value1	Value1	Value1	Value1
Value2	Value1	Value2	Value2
Value3	Value2	Value1	Value1
Value4	Value2	Value2	Value2

Third Normal Form: In order to be in third normal form, the tables need to be in second normal form and it should not have any transitive dependency. The transitive dependency refers to the situation when attribute x is dependent on y and z is dependent on x but z is not dependent on y. For the above table it can be proposed that resource attributes are dependent on resource name but these attributes are not dependent on load_id. Therefore, new tables are as following.

loan_id	person_id	Resource_id	Loan attributes
Value1	Value1	Value1	Value1
Value2	Value1	Value2	Value2
Value3	Value2	Value1	Value1
Value4	Value2	Value2	Value2

Resource_id	Resource Attributes
Value1	Value1
Value2	Value2
Value1	Value1
Value2	Value2

Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems. Pearson.

Yu, X., Xia, Y., Pavlo, A., Sanchez, D., Rudolph, L., & Devadas, S. (2018). Sundial: harmonizing concurrency control and caching in a distributed OLTP database management system. Proceedings of the VLDB Endowment, 11(10), 1289-1302.