Assignment 4 idbs Normalization PDF

Preview

Summary

Normalization ...

Description

Assignment 4

Q1 ANS: Normalization: The process used to minimize data redundancy and dependency in a relational database is called normalization. There are three types: 1) First normal form 2) Second normal form 3) Third normal form

Part A Functional dependencies in the relation:  

Dependency shows constraint between set of attributes. Entities store, pet and customer details shown which are related to each other. Dependency shown in diagram:

Functional dependency 1: StoreName, PetName

→

QuantityOnHand

In this relation, quantity of the pet is functionally depending on store name and pet name.

Functional dependency 2: PetName

→

PetDescription, Price, Cost, SupplierName, ShippingTime

In this relation, PetDescription, Price, Cost, SupplierName, ShippingTime are functionally depend on PetName. Functional dependency 3: StoreName, PetName → Pet Description, Price, Cost, SupplierName, ShippingTime, QuantityOnHand, DateOfLastDelivery, DateOfLastPurchase, DeliveryDate1, DeliveryDate2, DeliveryDate3, DeliveryDate4, PurchaseDate1, PurchaseDate2, PurchaseDate3, PurchaseDate4, LastCustomerName, CustomerName1, CustomerName2, CustomerName3, CustomerName4 In this relation, all are functionally dependent on StoreName and PetName.

Part B Normal form of the relation:    

Structure is not in normal form. Repeating attributes in the diagram is removed and the relation is converted into first normal form with single instance of the repeating attributes. Now, the relation is in 1NF. Diagram show attributes given below

In the relation DateLastPurchase, DeliveryDate1, DeliveryDate2, DeliveryDate3, DeliveryDate4, PurchaseDate1, PurchaseDate2, PurchaseDate3, PurchaseDate4, LastCustomerName, CustomerName1, CustomerName2, CustomerName3, CustomerName4 are removed.

Part C Normalize data model in 3NF:

  

Data model shows the relational as well as functional dependencies. It maintains the data integrity in data base. Functional dependency is defined as the constraint which is found in two or more set of attributes

Diagram shows normalize design and dependencies:

In pic small cutting happens that’s why I write note. Note: Store ID is primary key in Store table and Pet ID is primary ket in Pet table.Others table they are foreign keys:

Q2 Part A Functional dependency exists when one attribute in a relation uniquely determines another attribute. In given table, functional dependencies are: AID →

ALname, AFname, AInst

BNbr → BName, BPublish AID, BNbr → PubCity, BPrice, AuthBRoyalty

It can be shown as:

Part B First Normal form: 

Relation should contain only atomic values and not contain repeating groups.

Part b Ans: The given table is not in 1NF because in the given table there is repeating groups of data BNbr with data 106 is repeating two times. Also some of the attributes are not atomic. As the table is not in 1NF, so it will not be in 2NF and 3NF.

Part C Considering the given table,there exists repeating groups. Due to this poor structural characteristics, the following error may occur: 1) Partial dependencies may occur. 2) Due to the presence of repeating group, there may be a possibility of the occurrence of data inconsistency.

Part D First step: 1) First convert the table into 1NF: 2nd Step: 1) A table to be in 2NF if: a) It is in first normal form b) All non key attributes are fully dependent on the primary key.There should not be any partial functionally dependencies. 2) In this table AID and BNbr are primary key and dependency shown in diagram:

3) Decompose this table is divided into three tables as shown below: 1) Author (AID, ALname, AFname, AInst) Primary key: AID 2) Book (BNbr, BName, BPublish) Primar key: BNbr 3) AuthorBookRoyalities (AID, BNbbr, PubCity, Bprice, AuthBRoyality) primary key: AID ,BNbr. 3rd Step: A table to be in 3NF: if 1) It is in 2NF 2) There is no transitive functionally dependencies In this table Author, Book, AuthorBookRoyalities, there is no transitive dependencies. Hence the table are in 3NF.

Part E ERD diagram:

Q3 Part A In the given data the functional dependencies is as follows: TransactionID → TransactionDate, TransactionTime, TransactionTotalAmount, TransactionTax CustomerID

→

CustomerName, CustomerEmail

ProductID

→

ProductDescription, ProductCategory, ProductManufacturerID, ManufacturerName, ProductListPrice, ProductPurchasePrice, ProductQuantity

TransactionID, ProductID

→

TransactionProductTotal

Part B A table to be in the 3NF, there are some rules which have to fulfill by table.The rules are: 1) It is in 2NF(Second normal form) 2) There is no transitive functionally dependencies In the given data there is no transitive functionally dependencies but the data is not in 2NF,so this table is not in 3NF. A table to be in the 2NF, there are some rules which have to fulfill by table.The rules are: 1) It is in 1NF(First normal form) 2) All non key attributes are fully dependent on the primary key.There should not be any partial functionally dependencies. In the given table there is partial functionally dependencies exist and the data is not in 1NF also so this table is not in 2NF. A table to be in 1NF when it satisfies two constraints:

1) There should be no repeating groups in the relation. 2) A primary key is such a key with which each row in a relation can be uniquely identifies. In the given data there are repeating groups so this table is not in 1NF. Finally the data is not in any NORMAL FORM.

Part C Step 1: A table to be in 1NF when it satisfies two constraints: 1) There should be no repeating groups in the relation. 2) A primary key is such a key with which each row in a relation can be uniquely identifies In the given data repeating group property violets of 1NF.With the help of multiple entries, the violating can be removed.

Step 2: A table to be in the 2NF, there are some rules which have to fulfill by table.The rules are: 1) It is in 1NF(First normal form) 2) All non key attributes are fully dependent on the primary key.There should not be any partial functionally dependencies. In the table partially dependencies exist which can be removed by dividing the table into such parts with which each attribute off every table can be identified with the help of primary key.In the following diagram there is partially dependencies :

Given table in above diagram divide into four parts : 1) Transaction (TransactionID ,TransactionDate,TransactionTime, TransactionTotalAmount, TransactionTax, TransactionProductTotal) 2) Customer (CustomerID, CustomerName, CustomerEmail) 3) Product (ProductID, ProductDescription, ProductCategory, ProductListPrice, ProductPurchasePrice,ProductQuantity) In the given table there is no partial dependencies and the tables is already in 1NF So this table is in 2NF .After it people have to change in the 3NF.

Step 3: A table to be in the 3NF, there are some rules which have to fulfill by table.The rules are: 1) It is in 2NF(Second normal form) 2) There is no transitive functionally dependencies

Transitive functionality dependencies : The tables is already in 2NF and there is no transitive functionality dependencies between the attribute so this table is in 3NF awesome

Part D ERD diagram:

Q4 Part A Functional dependencies between the attributes: Functional dependencies our mapped in a diagram and it is shown below :

Functional dependency 1: MovieNbr, MovieLicenseType → MovieRentalPrice Functional dependency 2: MovieNbr, Character → ActorID Functional dependency 3: MovieNbr → Title, DirectorID, StudioID Functional dependency 4: StudioID → StudioName, StudioLocation, StudioCEO Functional dependency 5: DirectorID → DirectorName Functional dependency 6: ActorID → ActorName Functional dependency 7: MovieNbr → MovieLicenseType Functional dependency 8: MovieNbr, MovieLicenseNbr → LicenseRentalStatus, LicenseReturnDate

Part B Reason for the relation Is not in 3NF: This relation Is not shown in 3NF because there are multiple issues within the relation. It contains both partial functional dependency and transitive dependency ,in sample data given In textbook it is clearly seen the relation consist of repeating groups,so the relation is not in 1NF also. According to definition: A table to be in the 3NF, there are some rules which have to fulfill by table.The rules are: 1) It is in 2NF(Second normal form)

2) There is no transitive functionally dependency.

Partial functional dependencies in relation: Partial functional dependency:   

In a table is one or more non primary key attribute is partially dependent on the primary key attribute is called partial functional dependency. In the relation, other attributes are dependent on the full primary key attributes made up off MovieNbr and MovieLicenseNbr attributes . hence ,other attributes are partially dependent on the primary key attributes.

Transitive functional dependencies in relation: Transitive functional dependency:  



If any non primary key attributes are functionally depending on any other non primary key attribute in the relation then it is called transitive dependency. Non primary key attribute DirectorID gives director information Non primary key attribute StudioID give studio information and ActorID determines the actor information . Here non primary key attribute VendorID is depending on VendorSupportID .

Partial dependency and transitive dependency in relation must be removed to decompose the relation in 3NF.

Part C Based on assumptions and some constraints, the attributes as per the position of relations in 3rd normal form as follows : Note: In the relations , to indicate primary and foreign keys notational conventions are used  

attributes noted with underlined name are primary key values. attributes noted with italics letters are foreign key values

MOVIE relation: MOVIE (MovieNbr, Title, DirectorID, StudioID)

DIRECTOR relation: DIRECTOR (DirectorID, DirectorName) STUDIO relation: STUDIO (StudioID, StudioName, StudioLocation, StudioCEO) ACTOR relation: ACTOR (ActorID, ActorName) MOVIE CHARACTER relation: MOVIE CHARACTER (MovieNbr, Character, ActorID) MOVIE LICENSE relation: MOVIE LICENSE (MovieNbr, MovieLicenseNbr, MovieLicenseType, LicenseRentalStatus, LicenseReturnDate) MOVIE RENTAL PRICE relation: MOVIE RENTAL PRICE (MovieNbr, MovieLicenseType, MovieRentalPrice)

Part D ERD diagram:

Relational Model Q1) Queries also shows in screenshot: View Created Query: CREATE OR REPLACE VIEW SeniorEmp ( sname, sage, salary ) AS SELECT E.ename, E.age, E.salary FROM EMP5_T E WHERE E.age > 50; SELECT * FROM SeniorEmp;

Q2) Query: SELECT S.sname FROM SeniorEmp S WHERE S.salary > 100000;

The system will do the process like that:

System will select from view SeniorEmp where salary is greater then 100,000 and alias S is used for SeniorEmp view. Query: SELECT S.ename FROM ( SELECT E.ename AS name, E.age, E.salary FROM EMP5_T E WHERE E.age > 50 ) AS S WHERE S.salary > 100000;

Q3) View created Query: CREATE OR REPLACE VIEW SeniorEmp (

sname, sage, salary ) AS SELECT E.ename, E.age, E.salary FROM EMP5_T E WHERE E.age > 50;

Before update:

Insert into table:

View table update automatically:

Q4) Update check view is created using aggregation and group by operator so if we want to update the age it can't remove or update the view and original table.so it raises the error.so the conclusion is we can't update views in some cases like when views created using aggregation and group by operators Query: CREATE OR REPLACE VIEW AvgSalary (age, avgSalary) AS SELECT E.age,AVG(E.salary) FROM EMP5_T E GROUP BY E.age;

SQL Part Employee Table:

Dept table:

Work Table:

Q1) Query: SELECT E.ename, E.age FROM EMP5_T E, WORKS_T1 W, DEPTS_T D WHERE E.eid = W.eid AND W.did = D.did AND D.dname IN('Software','Hardware');

Q2) Query: SELECT W.did, COUNT (W.eid) FROM WORKS_T W GROUP BY W.did HAVING 1 > ( SELECT SUM (W.pcttime) FROM WORKS_T1 W WHERE W.did = W.did );

Q3) Query: SELECT E.ename FROM EMP5_T E WHERE E.salary > ALL (SELECT D.budget FROM DEPTS_T D, WORKS_T1 W WHERE E.eid = W.eid AND D.did = W.did );

Q4) Query: SELECT DISTINCT D.managerid FROM DEPTS_T D WHERE D.budget>1000000;

Q5) Query: SELECT E.ename FROM EMP5_T E WHERE E.eid IN (SELECT D.managerid FROM DEPTS_T D WHERE D.budget = (SELECT MAX (D.budget) FROM DEPTS_T D ) );

Q6)

Query: SELECT D.managerid FROM DEPTS_T D WHERE 5000000 < (SELECT SUM (D2.budget) FROM DEPTS_T D2 WHERE D.managerid = D2.managerid ) GROUP BY managerid;

Q7) Query: SELECT managerid FROM (SELECT SUM(budget) Total, Managerid

FROM DEPTS_T GROUP BY Managerid ORDER BY Total DESC ) WHERE ROWNUM=10000) )

Q9) We cannot use subquery in check so its give error: Query: CREATE TABLE DEPT_TS ( did

INTEGER,

budget REAL, managerid INTEGER, PRIMARY KEY (did), FOREIGN KEY (managerid) REFERENCES EMP5_T(eid), CHECK( (SELECT E.age FROM EMP5_T E WHERE E.ege>30 )) );

Q10) It give error because we cannot use check with more than one table and also assertion is not perofromed in oracle 12c. Query: CREATE TABLE DEPT_TS ( did

INTEGER,

budget REAL, managerid INTEGER, PRIMARY KEY (did) );

When create assertion: Query: CREATE ASSERTION managerAge AS CHECK ( (SELECT E.age FROM EMP5_T E, DEPT_TS D WHERE E.eid = D.managerid ) > 30 ) ;

Q11) It give error because we cannot use check with more than one table and also assertion is not perofromed in oracle 12c. Oracle does not support assertion so we can not performed: Q12) We use on delete cascade and on set null in table. Query: CREATE TABLE Emp7_T ( eid INTEGER, ename CHAR(10), age INTEGER, salary REAL, PRIMARY KEY (eid) );

Query: CREATE TABLE WORKS_T2 (eid INTEGER, did INTEGER, pcttime INTEGER, PRIMARY KEY(eid, did), FOREIGN KEY(did) REFERENCES Dept, FOREIGN KEY(eid) REFERENCES Emp ON DELETE CASCADE);

Query: CREATE TABLE DEPTS_T2 ( did INTEGER, buget REAL, managerid INTEGER, PRIMARY KEY(did), FOREIGN KEY(managerid) REFERENCES Emp ON DELETE SET NULL);

Q13) Oracle does not support assertion or accessing data from refranced tables in trigger or check statements: so its give error: Query: CREATE TRIGGER EmpMnager AFTER UPDATE OF salary ON EMP5_T REFERENCING OLD ROW AS OldTuple, NEW ROW OldTuple.salary) UPDATE EMP5_T SET salary = NewTuple.salary WHERE eid IN (SELECT E.eid FROM EMP5_T AS E, WORKS_T1 AS w,

AS NewTuple FOR EACH ROW WHEN (NewTuple.salary >

DEPTS_T AS d WHERE NewTuple.eid = w.eid AND w.did

= d.did

AND d.managerid = E.eid AND E.salary

< NewTuple.salary

);

Q14) Oracle does not support assertion or accessing data from refranced tables in trigger or check statements: so its give error:

Query: CREATE TRIGGER GiveRaise AFTER UPDATE ON EMP5_T WHEN old.salary < new.salary FOR EACH ROW

DECLARE raise REAL; BEGIN raise := new.salary - old.salary; UPDATE Emp M SET M.Salary = new.salary WHERE M.salary < new.salary AND M.eid IN (SELECT D.mangerid FROM EMP5_T E, WORKS_T1 W, DEPTS_T D WHERE E.eid = new.eid AND E.eid = W.eid AND W.did = D.did); UPDATE Dept D SET D.budget = D.budget + raise WHERE D.did IN (SELECT W.did FROM EMP5_T E, WORKS_T1 W, DEPTS_T D WHERE E.eid = new.eid AND E.eid = W.eid AND D.did = W.did AND D.budget < (SELECT Sum(E2.salary) FROM EMP5_T E2, WORKS_T1 W2 WHERE E2.eid = W2.eid AND W2.dept = D.did));

END

Q15) We cannot use subquery in check so its give error. Query: CREATE TABLE Work5_T ( eid

INTEGER,

did

INTEGER,

pcttime INTEGER, PRIMARY KEY (eid, did), CHECK ( (SELECT COUNT (W.eid) FROM Work5_T W GROUP BY W.eid

HAVING SUM(pcttime) > 100 ) );

= 0)...