data base
w350500
final_homework_assignment1.pptxFinal Homework Assignment: worth 4-homeworks! You must create a new database with at least three tables by: 1. Creating an ER diagram 2. Listing the functional dependencies, confirming that the database is in at least 4NF 3. Using SQL commands to create the and populate the database (there do not need to be many rows) 4. Creating four example SQL queries that would use the database that include: a. an aggregate function b. a join c. a nested query
Animal ID
Cage ID
ANIMAL
Location
Feeding Time
Cleaning Time
LOCATION
Animal Type
Animal Food
Vet
TYPE
Part 1:
Create an ER diagram
Functional dependencies:
animalId → (AnimalType,cageid,feedingtime, location,animalfood,cleaningtime,vet)
location → (cleaningtime,feedingtime)
AnimalType → (animalfood,vet)
Part 2: List the functional dependencies, plan the
database to be in 4NF
Animal ID
Cage ID
Animal Type
Location
ANIMAL
Location
Feeding Time
Cleaning Time
LOCATION
Animal Type
Animal Food
Vet
TYPE
Notice this includes inserting
foreign keys and identifying needed
referential integrity constraints
PK
FK
FK
PK
PK
CREATE TABLE TYPE
(
AnimalType char(24) NOT NULL,
AnimalFood char(24) NOT NULL,
Vet char(24) NOT NULL,
PRIMARY KEY (AnimalType)
);
CREATE TABLE LOCATION
(
Location Integer NOT NULL,
FeedingTime time NOT NULL,
CleaningTime time NOT NULL,
PRIMARY KEY (Location)
);
Part 3: Write the SQL to create and populate the tables
CREATE TABLE ANIMAL
(
AnimalID Integer NOT NULL,
CageID Integer NOT NULL,
AnimalType char(24) NOT NULL,
Location Integer NOT NULL,
PRIMARY KEY (AnimalID),
FOREIGN KEY (AnimalType) REFERENCES Type(AnimalType),
FOREIGN KEY (Location) REFERENCES Location(Location)
);
Part 3: Write the SQL to create and populate the tables
INSERT INTO Location VALUES (1,'9:00','14:00');
INSERT INTO Location VALUES (2,'11:00','9:00');
INSERT INTO Location VALUES (4,'13:00','15:00');
INSERT INTO Location VALUES (3,'14:00','13:00');
INSERT INTO Location VALUES (5,'10:00','8:00');
INSERT INTO TYPE VALUES ('African Lion','beef','Dr. Linch');
INSERT INTO TYPE VALUES ('Bengal Tiger','beef','Dr. Linch');
INSERT INTO TYPE VALUES ('Penguin','fish','Dr. Sidney');
INSERT INTO TYPE VALUES ('Rhinoceros','grass','Dr. Snow');
INSERT INTO TYPE VALUES ('Hippopotamus','grass','Dr. Snow');
INSERT INTO TYPE VALUES ('Ostrich','insects','Dr. Sidney');
INSERT INTO TYPE VALUES ('Spider Monkey','fruit','Dr. Linch');
INSERT INTO TYPE VALUES ('Sloth','leaves','Dr. Van');
INSERT INTO TYPE VALUES ('FruitBat','fruit','Dr. Van');
INSERT INTO TYPE VALUES ('Sea Lion','fish','Dr. Van');
INSERT INTO ANIMAL VALUES (26964,'African Lion',3856,1);
INSERT INTO ANIMAL VALUES (72235,'African Lion',3856,1);
INSERT INTO ANIMAL VALUES (54575,'Bengal Tiger',9398,1);
INSERT INTO ANIMAL VALUES (99382,'Penguin',9648,2);
INSERT INTO ANIMAL VALUES (70387,'Rhinoceros',4843,4);
INSERT INTO ANIMAL VALUES (38481,'Rhinoceros',4843,4);
INSERT INTO ANIMAL VALUES (47374,'Hippopotamus',1154,4);
INSERT INTO ANIMAL VALUES (29189,'Ostrich',3454,4);
INSERT INTO ANIMAL VALUES (74445,'Spider Monkey',8399,3);
INSERT INTO ANIMAL VALUES (61317,'Spider Monkey',8399,3);
INSERT INTO ANIMAL VALUES (46591,'Spider Monkey',4832,3);
INSERT INTO ANIMAL VALUES (48407,'Sloth',6690,3);
INSERT INTO ANIMAL VALUES (23068,'FruitBat',3265,5);
INSERT INTO ANIMAL VALUES (12946,'Sea Lion',8922,2);
Part 4: Write four SQL queries, including an aggregate function, join, and nested query
SELECT location, COUNT(*) as nbr_animals
FROM ANIMAL
GROUP BY location;
SELECT AnimalID, A.AnimalType, Vet, CageID
FROM ANIMAL A,
TYPE B
WHERE A.AnimalType=B.AnimalType;
SELECT *
FROM ANIMAL WHERE AnimalType IN
(SELECT DISTINCT AnimalType
FROM TYPE
WHERE vet=‘Dr. Snow’);
Plus One more (non-trivial)
SQL query
