This course is designed to familiarize students with SQL (Structured Query Language) in a relational database environment to include database programming and development. A series of database application assignments using SQL commands is designed to promote competency in ER (Entity Relationship) database modeling, database creation, database programming, and database optimization. The objective is to build a working knowledge and hands-on understanding of SQL.
Determine the functional dependencies that exist in the following table and then convert this table to an equivalent collection of tables that are in third normal form.
OFFICE_NUM -> OFFICE_NAME
ADDRESS -> SQR-FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM
OFFICE (OFFICE_NUM, OFFICE_NAME)
PROPERTY (ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM))
Determine the functional dependencies that exist in the following table and then convert this table to an equivalent collection of tables that are in third normal form.
PROPERTY_ID -> OFFICE_NUM, ADDRESS
ADDRESS -> SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM
OWNER_NUM -> LAST_NAME, FIRST_NAME
PROPERTY (PROPERTY_ID, OFFICE_NUM, ADDRESS)
LOCATION (ADDRESS, SQR_FT, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM)
OWNER (OWNER_NUM, LAST_NAME, FIRST_NAME)
StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice.
For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address.
STUDENT (STUDENT_NO, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, ADDRESS, CITY, STATE, POSTAL_CODE, TELEPHONE_NO, EMAIL)
For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate.
PROPERTY (OFFICE_NO, ADDRESS, CITY, STATE, POSTAL_CODE, SQR_FT, BDRMS, FLOORS, MAX_PERSONS, WEEKLY_RATE)
For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.
RENTAL (RENTER_NO, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, ADDRESS, CITY, STATE, POSTAL_CODE, TELEPHONE_NO, START_DATE, END_DATE, WEEKLY_RENTAL)
1. Create a table named SUMMER_SCHOOL_RENTALS. The table has the same structure as the PROPERTY table shown in Figure 3-48 except the PROPERTY_ID and OFFICE_NUMBER columns should use the NUMBER data type and the MONTHLY_RENT column should be changed to WEEKLY_RENT. Execute the command to describe the layout and characteristics of the SUMMER_SCHOOL_RENTALS table.
2. Add the following row to the SUMMER_SCHOOL_RENTALS table: property ID: 13; office ID: 1; address: 5867 Goodwin Ave; square feet: 1,650; bedrooms: 2; floors 1; weekly rent: 400; owner number: CO103.
4. Run the script file for the StayWell database to create the six tables and add records to the tables. Be sure to select the script file for the particular DBMS that you are using (MySQL, Oracle, or SQL Server). (Note: If you do not have the script files for this text, ask your instructor for assistance.)
- I believe I did this correctly, I’m not sure what to put for this question but I clicked SQL scripts in oracle and uploaded the file with Oracle at the end of the name.
5. Confirm that you have created the tables correctly by describing each table and comparing the results to Figures 3-48.
6. Confirm that you have added all data correctly by viewing the data in each table and comparing the results to Figures 1-4, 1-5, 1-6, 1-7, 1-8 and 1-9 in Module 1.
Below are screenshots showing I have all the correct data, residents I copied from excel because it was taking multiple screenshots. Also, I had issues with the script for oracle when it came to adding the dates in the SERVICE_REQUESTS table so I had to add each row manually, after reformatting the dates. Then the table wasn’t ordered by SERVICE_ID even though I had it set as the primary key, so I ran a query that ordered it by SERVICE_ID for the screenshot below.
