Scribd
Upload
87 views4 pages

Employee: Eemail F - Name M - Name L - Name Avantika University Er - Model

The document describes the entity relationship diagram and relational schema for Avantika University. It then analyzes the functional dependencies to check if the relations are in 3rd normal form. It is found that some relations need to be decomposed into separate relations to satisfy 3NF. The relations for Employee, Student and Department are decomposed into separate relations containing either a superkey or prime attributes to ensure 3NF. Other tables like Takes, Opts, Works_in and Teaches contain foreign keys and satisfy 3NF after decomposing the main tables.

Uploaded by

Sheikh Muhammed Tadeeb
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
87 views4 pages

Employee: Eemail F - Name M - Name L - Name Avantika University Er - Model

The document describes the entity relationship diagram and relational schema for Avantika University. It then analyzes the functional dependencies to check if the relations are in 3rd normal form. It is found that some relations need to be decomposed into separate relations to satisfy 3NF. The relations for Employee, Student and Department are decomposed into separate relations containing either a superkey or prime attributes to ensure 3NF. Other tables like Takes, Opts, Works_in and Teaches contain foreign keys and satisfy 3NF after decomposing the main tables.

Uploaded by

Sheikh Muhammed Tadeeb
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Avantika University

ER _MODEL eEmail
ePhn
Eid F_Name

Age Employee eName M_Name

eUser L_Name
salary
ePass ISA
w D_ID

Works Department D_name


Faculty
in
Str_date endDate
Takes D_location
details
Teach
Course
S_year F_Name
Result
Credits Opts Name M_Name
courseID Student

S_pass L_Name
S_email
S_phn
S_userN
Avantika University
Relational Schema

Result
Checking & Building our
relations in 3NF

(α -> β) Functional dependency in Employee (α -> β) Functional dependency in department


α β α β
E_id F_name, M_name, L_name D_id D_name
Euser, Epass Salary D_name Dlocation

R ( E_id, F_name, M_name, L_name, Euser, Epass, Salary) R (D_id, D_name, D_location)

(Well, In the Above case 1st relation satisfies the condition (Well, In the Above case 1st relation satisfies the condition
of 3rd normal form i.e. E_id is a super key but the second of 3rd normal form i.e. D_id is a super key but the second
relation is not in 3rd normal form as it doesn’t satisfies both relation is not in 3rd normal form as it doesn’t satisfies both
the condition to be in 3NF i.e. the condition to be in 3NF i.e.

1) Either α should be a super key. 1) Either α should be a super key.


2) Or β should be Prime attribute. 2) Or β should be Prime attribute.

So to solve this issue we will Make Separate Relation i.e. So to solve this issue we will Make Separate Relation i.e.

1) R (E_id, F_name, M_name, L_name) 1) R (E_id, F_name, M_name, L_name)


2) R (EUser, Epass, Salary) 2) R (D_name, Dlocation)
(α -> β) Functional dependency in Student (α -> β) Functional dependency in Course
α β α β
S_id F_name, M_name, L_name Course_id Credits,Start_date, End_date
Suser, Spass Result
R (Course_id, Credits, Start_date, End_date)
R ( S_id, F_name, M_name, L_name, Suser, Spass, Result)
(Well, In the Above case the relation satisfies the condition
(Well, In the Above case 1st relation satisfies the condition of 3rd normal form i.e. Course_id is a super key and primary
of 3rd normal form i.e. S_id is a super key but the second key both so it satisfies both the condition to be in 3NF i.e.
relation is not in 3rd normal form as it doesn’t satisfies both
5) Either α should be a super key.
the condition to be in 3NF i.e.
6) Or β should be Prime attribute.
3) Either α should be a super key.
4) Or β should be Prime attribute.

So to solve this issue we will Make Separate Relation i.e.

3) R (S_id, F_name, M_name, L_name)


4) R (SUser, Spass, Result)

Note : ALL OTHER TABLES PRESENT IN RELATION SCHEMA I.E. Takes, Opts, Works_in, Teaches contain foreign key of main table table
and once we redefined the main table in 3 NF , those tables also comes in 3NF.

You might also like