LSIS 5451 North Carolina Central University Database Systems Exam Practice

User Generated

oyhrpnfu

Programming

LSIS 5451

North Carolina Central University

LSIS

Description

Are you available to help me with this assignment? I attached a PDF with the assignment instructions.

Unformatted Attachment Preview

Database Systems LSIS 5451 Fall 2020 Final (Take Home) Examination Due Date: Friday, November 20th, 2020, 11:59 pm Instructions You are not allowed to discuss this exam with anyone. By submitting your answers, you are certifying that you did not give or receive any unpermitted aid in the exam. All answers should be your own. Late submission will not be accepted. Question 1 (20 points) Consider a database that includes four relations Student, Staff, Program, Advisor Student BannerID FName LName PCode 8003778543 James Oloye P001 8765990453 Paul Carter P002 8002786543 Anita Boris P001 8307563321 Jane Eyre P001 BannerID FName LName PCode 8014768954 Chimamanda Adichie P002 8001876565 Paulette Davis P001 Staff Program PCode PName P001 Library Science P002 Management and Information Science Advisor staffBannerID studentBannerID 8001876565 8307563321 8014768954 8003778543 8001876565 8002786543 a. Write the SQL statement that will return the following result (10 points) FName LName Program Students Chimamanda Adichie Management and Information Science 1 Paulette Davis Library Science 2 b. For each query below, indicate if the query correctly returns the name of all students who have not yet been assigned to an advisor. Anywhere between zero and all queries compute the correct answer. Each query is syntactically correct. (10 points) i. SELECT s.fname, s.lname FROM student s LEFT OUTER JOIN advisor a ON s.BannerID = a.studentBannerID WHERE a. staffBannerID IS NULL; ii. SELECT s.fname, s.lname FROM student s WHERE s.BannerID IN (SELECT a.studentBannerID FROM advisor a); iii. SELECT s.fname, s.lname FROM student s INNER JOIN advisor a ON s.BannerID = a.studentBannerID GROUP BY s. BannerID, s.fname, s.lname HAVING count(*) = 0; Question 2 (30 points) A company selling products both online and in their own stores have asked for your help in creating a database. a. Design an E-R model for the store that contains the following relations and their attributes. (15 points) • • • customer: cid, name sales: sid, date product: pid, name, price Model the following relationships among the relations: • Sales represents individual sales. Each sale has different products bought by a customer. • Each product in a sale could have a discount. • inStoreSales consists of the sales in brick-and-mortar stores; storing the store name. • onlineSales consists of online sales; storing the name of the browser and the operating system used during the purchase. • cid, sid, pid are int, price and discount are float, the rest are text. • No attributes may be null, except for inStoreSales.store and onlineSales.os. b. Write the CREATE TABLE statements to represent this E-R model using SQL relations. Clearly label all the constraints. (5 points) c. Write a SQL query that computes, for each customer, the total amounts that the customer spent online and in store. Your query should include customers who did not purchase anything. (10 points) Question 3 (25 points) Use the database of a Ski Resort, represented by the ER model above to answer the following questions: a. Explain the cardinality, participation and disjoint constraints that exists in the model (5 points) b. Find all customers that rented a pair of skis that cost over $200 (5 points) c. A skier should rent a pair of skis while a snowboarder should rent a snowboard. If a person rents a different equipment from their expertise, a training should have been completed. Write a query that finds all rentals in the past month with inconsistencies between expertise and equipment (8 points) d. The company is looking to purchase new equipment. Write a query that computes the average price of a pair of skis and snowboards for each model. Display your results sorted from the least price. (7 points) Question 4 (25 points) Consider a relational table: Manager(manager_name, manager_id, manager_office_id, intern_id, intern_name, intern_office_id, intern_designated_printer_id, printer_owner_id, printer_id, printer_model, secretary_name, secretary_id, secretary_office_id ) Suppose the data has the following properties: • • • • • • • • Managers and secretaries have individual offices, interns share offices. Interns can work for multiple managers. Printers are owned by one manager. Managers can own multiple printers. Interns can only use one printer. The printer the interns uses must be owned by one of the managers they work for. Secretaries can work for multiple managers. Managers only have a single secretary. a) Using the data above, provide examples of how insertion, deletion, and modification anomalies could occur. (5 points) b) Identify the functional dependencies among the columns in the table and state any assumptions you make about the data. (10 points) c) Using the functional dependencies identified in (b), describe and illustrate the process of converting the table into 3NF tables. Identify the primary and foreign keys in your tables. (10 points) ~ END OF EXAM ~ Thank you for making the class enjoyable and I hope you have learned a lot! Good luck with finals and have an wonderful ~2 months of winter break!
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached. Please let me know if you have any questions or need revisions.

Question 1
a)
SELECT distinct staff.FName as "FName", staff.LName as "LName",
program.PName as "Program", COUNT(student.BannerID) as "Students"
FROM staff
JOIN advisor
ON staff.BannerID=advisor.staffBannerID
JOIN student
ON student.BannerID=advisor.studentBannerID
JOIN program
ON program.PCode=staff.PCode
GROUP by staff.FName, staff.LName;
b)
I. Yes, it returns it will return Paul Carter.
II. No, it returns the name of those students who have been assigned an advisor i.e. James,
Anita and Jane have been assigned an advisor.
III. No, it will not return any record.
Question 2
a.

b.
CREATE TABLE sales (
sid int not null,
salesDate date not null,
PRIMARY KEY (sid)
);

CREATE TABLE inStoreSales (
sid int not null,
storeName varchar(30),
CONSTRAINT FK_SalesinStore FOREIGN KEY (sid)
REFERENCES sales(sid)
);

CREATE TABLE inOnlineSales (
sid int not null,
browserName varchar(30) not null,

operatingSystem varchar(30),
CONSTRAINT FK_SalesOnline FOREIGN KEY (sid)
REFERENCES sales(sid)
);

CREATE TABLE prod...

Related Tags