file added

Student Name
Student ID        2
INFO151 2018-T1 - Assignment 4
Submitted By
Course
Professor
Date
Answer 1
Identify the primary key of the above dependency diagram. What is the name given to this type of primary key? [2 marks]
The primary keys in the given diagram are A, D and G. This will be Composite primary key as
Functional dependency will be
A B, C
D E
A, D, G B, C, E, F
(b) What type of dependency is D E and AB,C? [2 marks]
D E represents partial dependency as E is dependent only on D rather than composite primary key.
A B, C represents Partial functional dependency as B, C are dependent on single primary key A.
A, D, G B, C, E, F represents functional dependency as B, C, E, F are dependent on composite primary key.
(c) Can (A,D,G) determine F in the above diagram? Justify your answer. [2 marks]
Yes, A,D, G can determine F according to the diagram because A,D,G represent composite primary key and their value remain unique and F is dependent on this composite primary key. Hence, it can be identified.
(d) Name two key attributes other than “A”. [2 marks]
Two key attributes are B and C which depends on A.
(e) Normalise the above dependency table to 3rd normal form. You must show the progress from 1NF to 2NF, and then to 3NF. Indicate all the primary keys and foreign keys in the normalised tables. [8 marks]
There will be three entities.
According to 1NF, the relationship will be according to given diagram.
AA (A, B, C, D, E, F, G)
According to 2NF, the relationship will become
    A
    B
    C
    
    A
    D
    E
    F
    G
Primary Key A + D + G
Foreign Key A
Tables will be in 2NF because these exhibits transitive dependencies.
According to 3NF Relationship
Table 1
    A
    B
    C
Primary Key A
Table 2
    D
    E
Primary Key D
Table 3
    A
    D
    G
    F
Primary Key A, D, G.
Foreign Key D, G.
(f) Write the DDL to create the final 3NF tables. For primary keys use ‘Integer’ data type, and for non-key attributes use ‘Text’ data type. Use TABLE1, TABLE2, and TABLE3 etc. for the table name. [4 marks]
Table 1
Create Table1 (A int Primary key, B Text(30), C Text (30));
Table 2
Create Table2 (D int Primary key, E text (30));
Table 3
Create Table3 (G int NOT NULL,
A int NOT NULL references Table1 (A),
D int NOT NULL references Table2 (D),
F text (30),
CONSTRAINT pk_AB PRIMARY KEY (A, D, G))
ANSWER 2
(a) From the student record form above, identify the attributes likely to form a repeated group. [2 marks]
Attributes that are likely to form a repeated group – Name, Address, Email, Course Summary.
(b) Transform the form into a table, then identify and write the primary key for the table. [4 marks]
Student Student ID, Name, Address, Email.
Primary...