Database Assigment 2 PDF

Preview

Summary

Database Assigment 2...

Description

COIS 3400, Assignment 2 COIS 3400, Assignment 2 Question 1 a. Farmer-Cow ER Model b. Patient-Physician Model c. Art researcher model Question 2 a. Farmer-Cow Table b. Patient-Physician Table Question 3 a. Create tables for this model. i. Person Table ii. Car Table b. Insert i. Person Insert ii. Car Insert c. Answer the following queries and show the output of your queries: i. Personid for a name ii. Last names sorted iii. Personel Sales iv. Count each car sale v. Average Selling price vi. Maximum selling price vii. Gross Profit for at least 3 cars viii. Gross profit for all Question 4 Sources:

          

Question 1 Draw Entity-Relationship models for the following situation. In each case, make certain that you show at least three attributes and key for each entity:

a. Farmer-Cow ER Model  

b. Patient-Physician Model 

c. Art researcher model 

Question 2 a. Farmer-Cow Table CREATE TABLE Farmer (farmer_id INTEGER,           name VARCHAR(64),           number_of_cows INTEGER,           PRIMARY KEY (farmer_id)) ) CREATE TABLE Cow(cow_id INTEGER,         name VARCHAR(64),         farmer_id INTEGER,         PRIMARY KEY (cow_id), FOREIGN KEY (farmer_id) REFERENCES Farmer);

b. Patient-Physician Table CREATE TABLE Patient (patient_ssn CHAR(11),           name VARCHAR(64),           age INTEGER,           address VARCHAR(64),           PRIMARY KEY (ssn)); CREATE TABLE Physician(physician_ssn CHAR(11),         name VARCHAR(64),         speciality VARCHAR(64),         PRIMARY KEY (physician_ssn)); CREATE TABLE consults(appointment DATETIME,           patient_ssn CHAR(11),           physician_ssn CHAR(11),           PRIMARY KEY (appointment),           FOREIGN KEY (patient_ssn) REFERENCES Patient(patient_ssn),           FOREIGN KEY (physician_ssn) REFERENCES Physician(physicial_ssn));

Question 3 a. Create tables for this model. DROP TABLE IF EXISTS Person; CREATE TABLE Person (personid VARCHAR(64) PRIMARY KEY NOT NULL, persfname VARCHAR(64), perslname VARCHAR(64)); DROP TABLE IF EXISTS Car; CREATE TABLE Car (carid VARCHAR(64) PRIMARY KEY NOT NULL, personid VARCHAR(64), carcost DECIMAL(8,2), carsell DECIMAL(8,2), cartype VARCHAR(64), FOREIGN KEY (personid) REFERENCES Person(personid));

i. Person Table  ii. Car Table 

b. Insert Data generated from: []: https://www.mockaroo.com/ i. Person Insert  ii. Car Insert 

c. Answer the following queries and show the output of your queries: i. Personid for a name SELECT * FROM `Person` WHERE perslname = 'Lampen'

 ii. Last names sorted SELECT persfname, perslname FROM `Person` ORDER BY perslname ASC, persfname

 iii. Personel Sales SELECT C.carsell, C.carcost, P.persfname, P.perslname FROM Person P, Car C WHERE C.personid = P.personid AND P.persfname = 'Sapphire'



iv. Count each car sale SELECT cartype, COUNT(*) FROM Car GROUP BY cartype ORDER BY 2 DESC

 v. Average Selling price SELECT P.persfname, P.perslname, AVG(C.carsell) FROM Car C, Person P WHERE P.personid = C.personid AND perslname = 'Greggersen'

 vi. Maximum selling price SELECT MAX(carsell) FROM Car

 vii. Gross Profit for at least 3 cars select count(C.personid), P.persfname, P.perslname, sum(C.carsell - C.carcost) as gross_profit from Person P, Car C WHERE C.personid=P.personid GROUP BY C.personid HAVING COUNT(C.personid)>=3

 viii. Gross profit for all SELECT C.carid, C.personid, C.carcost, C.carsell, C.cartype, C.carsell-C.carcost AS gross_profit FROM Car C, Person P WHERE C.personid=P.personid



Question 4 

Sources: []: https://www.w3resource.com/sql/aggregate-functions/count-having.php []: https://www.w3schools.com/sql/sql_foreignkey.asp []: https://ubiq.co/database-blog/how-to-calculate-margin-in-mysql/ ...