he homework is due on Feb.25 (Saturaday). Each student must submit one copy of the homework and one copy of the spool file. The following shows the file name format: Zno_FirstName_LastName_HW01.pdf Zno_FirstName_LastName_HW01_spool.txt Example) Z00000001_KwangSoo_Yang_HW01.pdf Z00000001_KwangSoo_Yang_HW01_spool.txt The pdf file should contain all answers for Question Part and all SQLs for Lab Part. Note that the number of tuples in the outcome of each SQL query is never greater than 50. Please do not use the zip file to upload the files. Upload two separate files. After submitting the homework, please download it and see if the submission is correct. Late submissions are not accepted after two days (i.e., Feb 27 (Monday)). Late penalty. 1st day: You lose -25 points. 2nd day: You lose -50 points. Question Part (50 points) Question 1 (20 points) We plan to construct a database for the FAU parking service shstem. The FAU has many parking-lots which can accommodate many vehicles. Each parking-lot has id, name, and locattion. A parking-lot consists of many parking spaces (or spots) and each parking space (or spots) has a unique number. Every customer has zno and name. A customer can purchase more than one parking-permit. A vehicle has id, brand, and color. A customer can own multiple vehicies. Each parking permit has no. and price. A parking-permit allows a vechicle to park in dedicated parking spaces (or spots). The FAU maintains a time table and uses it to record when a customer parks a vechicle at a dedicated parking space (or spot) at a specific time-slot in the time- table. A time-table has id, date, and hour.

Draw an ER diagram for this application. Specify key attributes of each entity type and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. Question 2 (20 points) Write DDL statements to create the tables for the ER diagram of Question1. Question 3 (10 points). Given a relation R (A, B, C, D, E). Answer the following questions with the given set of FDs: (1) identify candidate keys and (2) state the strongest normal form that R satisfies (e.g., 1NF, 2NF, 3NF, or BCNF) (a) FDs:AB,BA,BC,CD,C E (b) FDs:ABCDE,BCADE,CDEAB Lab Part (50 points) Preliminary 1) Login into the Linux machine (oraclelinux.eng.fau.edu) 2) Connect to database (e.g., sqlplus username/password) 3) ALTER SESSION SET CURRENT_SCHEMA = COP6731; 4) Please note that the schema name is defined as upper case characters. 5) Execute the following SQL and identify all required tables. SELECT table_name from all_tables where owner = 'COP6731'; Please note that the owner's name is defined as upper case characters. 6) Change line size: SET LINESIZE 400 Use the spool command to create a log file for the output of SQL (e.g., SPOOL filename and SPOOL OFF) Note: Submit both SQLs and SPOOL files (i.e., SQL statements and a SPOOLfile). Consider the following relational schemas.

Write SQL statements for the following queries. 1) Retrieve all names of products that were purchased by 'Kailani Benham' on 29-03- 23 at the store located at '22100 Fl-7, Boca Raton, Fl 33428'. 2) Retrieve the ids and the first and last names of employees who have no supervisor and are older than 80. 3) Retrievetheidsandthefirstandlastnamesofcustomerswhopurchasethe 'Seasense Rope Ladder' product. 4) Retrievetheidsandthefirstandlastnamesofcustomerswhopurchasenoitem. 5) Retrievetheidsandthefirstandlastnamesofcustomerswhopurchase'Seasense Rope Ladder' or 'Sloth Face Ice Hockey Puck' products. 6) Retrievetheidsandthefirstandlastnamesofcustomerswhopurchaseboth 'Seasense Rope Ladder' and 'Sloth Face Ice Hockey Puck' products. 7) Retrieve the first and last names of the oldest customers whose salary is greater than 400000. 8) Retrieve the names of brands whose brand value is greater than 98 and contains more than 10 products. 9) Retrieve the first and last names of customers who purchase and return the same item on the same date. 10)Retrieve the first and last names of customers who purchases all items of the 'Q-