What is the primary key of the student table

Assignment Help Database Management System

Reference no: EM131907766

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.

Reference no: EM131907766

Questions Cloud

Why would the measurements be valuable : Create two measure of innovation for your organization. Why would these measurements be valuable?

What types of data were collected for the research : What types of data were collected for the research? How was the collected data analyzed? Why do you want to include the collected data in your research project.

Distinguish among the concepts of denial and repression : Distinguish among the concepts of denial, repression, projection, reaction formation, and displacement. In which ways is Freud's thinking no longer valid?

Describe the proposal to use a hardware server network : Compare and contrast the proposal to use a Virtual Network versus a Hardware Server Network. Add a reference slide that identifies the resources.

What is the primary key of the student table : MISC 373 Final Examination - What is the primary key of the Student table? What are the problematic functional dependencies in the Student table? Name them

Do a comprehensive evaluation on an older adult : Cardiac and pulmonary function are interrelated. When a person develops a problem with one system the other is compromised. Please do a comprehensive evaluation

Write a summary on psychology article : Write a summary on psychology article. No more than 2 years old. The summary must consist a copy of the article describing the event.

Develop a business continuity plan for your organization : Develop a business continuity plan for your organization. Describe the basic activities that must be managed by the BCP.

Discuss potential pluses and minuses of being open to fdi : Discuss potential pluses and minuses of being open to FDI for Lesotho.

User Account

All Pages