Final Examination -
Q1. Short Answer Questions
1.1 - What are the four main things to be specified/drawn in the E-R diagram?
1.2 - What are the condition for relational tables to be in the 2NF?
1.3 - What are the conditions for relational tables to be in the 3NF?
1.4 - What is the difference between a regular database and a data-ware house in terms of their time horizons?
1.5 - What is the difference between a regular database and data-ware house in terms of their data details?
Q2. SQL Statements
Consider the following relational tables:
SalesPerson (SalesPersonID, SalesPersonName, Salary)
Customer (CustomerID, CustomerName, City)
SalesOrder (OrderNumber, CustomerID, SalesPersonID, Amount)
Please use SQL statements to answer the following questions:
2.1 - Show the names of all salespeople who earn more than $49,999 and less than $100,000 per year.
2.2 - Show each city and the number of customer for each city.
2.3 - Show the name of the salesperson with the highest salary.
2.4 - Show the names and salaries of salespersons who have an order with customer "John" (use a join).
2.5 - Show the names and salaries of salespersons who have an order with customer "John" (use a subquery).
Q3. Database Design
Develop a database to support the tracking needs of a university. The university wants to know: What student clubs does a student join (suppose a student can join several clubs or none, a club has many students). Which professor is the advisor of a club? (Suppose a professor can advice several clubs or none, and a club has exactly one professor as its advisor)? Please:
3.1 - Develop an E-R diagram for the database application.
3.2 - Map the E-R diagram to relational table structures. Each table should include table name, basic attributes, and the primary key. Please use an underline to indicate the primary key and a dashed line to indicate a foreign key if a table has.
Q4. Normalization of relational tables
Consider the following Student relational table and sample data (Note: This table is about student information)
Student
|
StudentID
|
StudentName
|
AdvisorName
|
AdvisorDepartment
|
|
6789
|
Rose
|
Dr. Zhou
|
Information Systems
|
|
7890
|
Bill
|
Dr. Smith
|
Management
|
|
8901
|
Dave
|
Dr. Smith
|
Management
|
|
9012
|
Bob
|
Dr. Zhou
|
Information Systems
|
|
0123
|
Bill
|
Dr. Zhou
|
Information Systems
|
|
1234
|
Susan
|
Dr. Parks
|
Management
|
Please answer the following questions:
4.1 - What is the primary key of the Student table?
4.2 - What are the problematic functional dependencies in the Student table? Name them.
4.3 - In what normal form is the Student table?
4.4 - Normalize the Student table to the 3NF. For each table, please use an underline to indicate the primary key and a dashed line to indicate a foreign key if a table has.