Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

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).
 

Requirement Specification

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.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). SEEC Database: Revised Content, EER Diagram, Data Dictionary, Normalization Essay.. Retrieved from https://myassignmenthelp.com/free-samples/info6001-database-management-for-storing-resource-record.

"SEEC Database: Revised Content, EER Diagram, Data Dictionary, Normalization Essay.." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/info6001-database-management-for-storing-resource-record.

My Assignment Help (2020) SEEC Database: Revised Content, EER Diagram, Data Dictionary, Normalization Essay. [Online]. Available from: https://myassignmenthelp.com/free-samples/info6001-database-management-for-storing-resource-record
[Accessed 12 June 2024].

My Assignment Help. 'SEEC Database: Revised Content, EER Diagram, Data Dictionary, Normalization Essay.' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/info6001-database-management-for-storing-resource-record> accessed 12 June 2024.

My Assignment Help. SEEC Database: Revised Content, EER Diagram, Data Dictionary, Normalization Essay. [Internet]. My Assignment Help. 2020 [cited 12 June 2024]. Available from: https://myassignmenthelp.com/free-samples/info6001-database-management-for-storing-resource-record.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close