Identify whether or not the following are in 2NF and/or 3NF.
If they are in 2NF, explain why. If they are not in 3NF, explain and decompose them to 3NF.
EMPLOYEE (EMPLOYEE-No, SECTION-No)
CLASS (COURSE-No, SECTION-No, ROOM)
CLASS (COURSE-No, ROOM, CAPACITY, COURSE-NAME)
A table would be in 2NF iff:
So,all the 3 tables would be in 2NF as we have only one primary key and all other columns except primary key(primary attribute's) are dependent on any other primary key(If we are having more than one candidate key for a table in that case).
So,
1)EMPLOYEE (EMPLOYEE-No, SECTION-No):Employee-No is only the primary key and SECTION_No is dependent on it.
2)CLASS (COURSE-No, SECTION-No, ROOM):Here we should have only one primary key(Let it be COURSE-No) and no other candiate key and all other non-prime attribute are dependent on it.
3)Same as "2".
3NF:
A table design is said to be in 3NF if both the following conditions hold:
An attribute that is not part of any candidate key is known as non-prime attribute.
In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency X-> Y at least one of the following conditions hold:
An attribute that is a part of one of the candidate keys is known as prime attribute.
Transitive dependency means:
If "A" is dependent on "B" and "B" is dependent on "C".
A->B->C.
For 3NF we should have only one level of dependecies:
A->B
B->C.
In our example's:;
Here as we have only two columns so we 3NF is there.
2)CLASS (COURSE-No, SECTION-No, ROOM):
Here we Section no is dependent on Course-no and Then Room is dependent on SECTION-No,so here 3NF is not there.
TO Convert it into 3Nf:
CLASS(COURSE-No,SECTION-No)
CLASS(Section-No,ROOM)
3)CLASS (COURSE-No, ROOM, CAPACITY, COURSE-NAME):
Theory is same as"2:.
Converting it to 3Nf:
CLASS(COURSE-No,COURSE-NAME,SECTION-No)
CLASS(Section-No,ROOM)
CLASS(Room,Capacty)
Identify whether or not the following are in 2NF and/or 3NF. If they are in 2NF,...
Decompose the given relation into relations in 2NF. For each of the new relations, draw a functional dependency diagram, and state if it is in 3NF and why. If your answer is no, describe how to normalise the relation to be in 3NF (c) The relation appointment is given below: appointment (patientID, patientName, patientAddress, aDate, aTime, nurseiD, nurseName, notes) where the primary key is underlined. It records the details of patient appointments with nurses. (i) Draw the functional dependency diagram...
CIS 3350 – Assignment #4 Transform the following two unnormalized datastructures to 1NF, 2NF and 3NF structures. Unnormalized student table (5 points) STUDENT (STUDENT-NUMBER, STUDENT-NAME, TOTAL-CREDITS, GPA, ADVISOR-NUMBER, ADVISOR-NAME, (COURSE-NUMBER, COURSE-DESC, NUM-CREDITS, GRADE)) Sales Order table (5 points) SalesOrder (SalesOrderNo, Date, CustomerNo, CustomerName, CutomerAddress, ClerkNo, ClerkName, (ItemNo, ItemDescription, ItemQuantity, ItemUnitPrice), Total)
Normalize the un-normalized table given below through INF, 2NF and 3NF. Show the normalization process in a file (e.g., creating tables and drawing lines in MS Word or MS Excel), save as a .pdf file, and submit it through the link available on Canvas. Name your file according to this scheme: yourlastname a3_normalize.pdf. Do not protect your .pdf file. The presentation of your normalization process should mimic the normalization class exercise and the example provided on Canvas In order to...
SQL 2. Decompose the following relation to conform with 2NF and 3NF. Custoner Retal Relation CR76 CR76 CRSA Tine Murphy P016 CRs6 PO16 Prinvey Key fdi ds id4 fas Spelling out the functional dependencies, we have the following: 2 Cuetomer No 3. Property No CNane - RAddress. Rent, Oner ne. OName > QNane PToPerty No Paddress. Rentrintsh 5Customer No, Rentstart Rent, 9wnex Ne. 9name 2. Decompose the following relation to conform with 2NF and 3NF. Custoner Retal Relation CR76 CR76...
4. (40 points) Identify the following relations that are in Third normal form (3NF)? If it is in 3NF, is it in BCNF? You must explain your answer to receive points. Also, for each question, pick one FD not in BCNF and decompose R into tables if it is not in BCNF. (2) R(ABCD) FD's: AB-»C; ABD->C; ABC-> D; AC-ID (4) R(ABCD) FD's: C- B;A->B; CD -»A; BCD (5) R(ABCD) FD's: AD C;D-A;A-C;ABC A D, 4. (40 points) Identify the...
4. (40 points) Identify the following relations that are in Third normal form (3NF)? If it is in 3NF, is it in BCNF? You must explain your answer to receive points. Also, for each question, pick one FD not in BCNF and decompose R into tables if it is not in BCNF (1) R(ABCD) FD's: ACD B;AC D D C;ACB (2) R(ABCD) FD's: AB C; ABD C;ABC D;ACD (3) R(ABCD) FD's: AB; B A;A D D B (4) R(ABCD) FD's:...
Consider the following relation for published books: BOOK(Book title, Author_name, Book type, List price, Author affil, Publisher) Author_affil refers to the affiliation of author. Suppose the following dependencies exist: Book title Publisher, Book type Book type List price Author_name > Author affil (a) What normal form is the Book relation in? Why? (b) Decompose Book into 2NF relations. . (c) Decompose Book into 3NF relations.
Normalization Diagram: Create a 1NF, 2NF and 3NF diagram from the above spreadsheet. A 2NF diagram may not be needed. ER Diagram: Create an EERD from the above spreadsheet and the Normalization Diagram in Step 1. Use the following business rules: - Employees are either Volunteers or Paid Employees but not both. - A Paid Employee is either a Contractor or works directly for the zoo, but can't be both. - Everyone is assigned a department, and assigned to a...
Normalize EVERY relation to 3NF step by step Q1: Given the following table, identify the functional dependencies VisitID InvoiceDate Invoice Amt Invoiceltem 1002 11/09/2016 $65.00 Updated shots 1002 11/09/2016 $45.00 Flea & tick medications 1006 11/14/2016 $35.00 Heartworm medication 1006 11/14/2016 $65.00 Updated shots 1006 11/14/2016 $75.00 Lab work 1009 11/16/2016 $50.00 Grooming 1009 11/16/2016 $15.00 Nail trim 1012 11/21/2016 $65.00 Updated shots 1012 11/21/2016 $75.00 Lab work 1013 11/21/2016 $65.00 Updated shots 1013 11/21/2016 $75.00 Lab work 1014 11/21/2016...
The following are the main entity types of the academic institution database. For each entity type, we provide some of the corresponding attributes. Use this information in order to: (a) Build an Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database. Identify the primary key(s) and the foreign key(s) for each relation. Draw the relational integrality constraints; (c) For each of the relations created, indicate its normal form. If the relation is not in the 3NF,...