1) Orland school district 135 assigns substitute teachers to different schools within its district. The table below lists the time spent by substitute teachers working at various schools for a given academic year. Each teacher has a unique ID. Each school has a unique ID. Each substitute teacher assignment to a given school has a unique assignmentNo. hoursWorked describe the number of hours worked for the given assignment.
(a) The table shown below is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies.
(b) Identify the functional dependencies represented by the attributes shown in the table below. State any assumptions that you may see necessary based on the given data.
(c) Describe and illustrate the process of normalizing the table shown below to 3NF. Identify primary and foreign keys in your relations.
assignmentNo teacherID teacherFName techerLName schoolID schoolName hoursWorked
1001 T1105 John Smith S101 Center School 30
1002 T1107 Tracy Carter S102 Liberty School 25
1003 T1101 Tom Smith S103 Jerling Jr.High 35
1004 T1105 John Smith S101 Center School 15
1005 T1107 Tracy Carter S103 Jerling Jr.High 15
1006 T1107 Tracy Carter S103 Jerling Jr.High 20
2) The department of Computer Science at North Western University needs to maintain records about its students, courses and faculty members. A faculty member can teach one or more courses per semester. A student can register for one or more courses per semester. For each student you need to maintain the student ID, first name, last name, address and phone number. You also need to show the courses a student is taking in the current semester. For each faculty member you need to maintain the faculty ID, first name, last name, address and phone number. You also need to show the courses a faculty is teaching in the current semester. Assume there is only one section for each course, and the course is taught by one faculty member. For each course you need to maintain the course ID and course name. You are required to provide the following:
a. Create a relational schema for the description provided above
b. Provide sample data for the tables you proposed
c. Create an ER diagram for the description provided above, show the multiplicity, participation and cardinality between related relations.
3) Review the tables of Figure 4.3 on page 104, write the SQL commands for the following:
a. List full details of all branches
b. List the branch number and branch city for each branch
c. List all staff numbers for staff who manage properties. Eliminate duplicates from your list.
d. List all property numbers and the monthly rent, where rent is less than $450.00 per month.
e. Provide a list for all properties (propertyNo), the city name, and rent for all properties NOT IN Glasgow.
f. Produce a list of properties and their annual rent for all properties arranged in descending order of rent. The rent provided in the given tables is monthly.
g. How many staff receive a salary greater than $20,000.00
h. Provide a list of staff names and salaries for all staff who manage properties and their salaries are less than $15000. (This is a case where you need to join two tables).
i. Provide a list of properties that have been viewed, the manager (staffNo) assigned to the viewed property, and the first and last names of the staff. Eliminate duplicates
j. Insert a new branch to the branch table. (Use sample data of your choice).