1 / 27

Mid-term Exam Answer Keys

Mid-term Exam Answer Keys. Week 7 Spring 2011. I. Multiple Choices Questions . Each may have more than one answer ( 6 x 4 = 24 points total). I.1. Which of the following statements about database systems is (are) not right?

primo
Download Presentation

Mid-term Exam Answer Keys

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Mid-term Exam Answer Keys Week 7 Spring 2011

  2. I. Multiple Choices Questions Each may have more than one answer (6 x 4 = 24 points total)

  3. I.1 • Which of the following statements about database systems is (are) not right? • A. Business intelligence refers to a bunch of computer-based techniques. • B. Relational database model is the most popular database models in use today • C. A DBMS is the software you use to create and work with a database. • D. Microsoft Access is a network model because it allows many-to-many relationships. • D. Access is a database tool which is irrelevant to database model.

  4. I.2 • If I were creating a table and I wanted a particular value to be populated automatically in a field but the user could enter a new value in its place if desired, I would create which of the following: • A. Check constraint • B. Default constraint • C. Cascade constraint • D. Define constraint • E. None of the above • B.

  5. I.3 • Which of the following associated relationships is (are) M:N? • A. Dad:: Child • B. Customer:: Hotel Room • C. Traveler:: Flight • D. Product:: Review • E: Owner:: Pet • Not A or D

  6. I.4 • When do you need a whereclause in a SQL statement? • A. When you are filtering so that only records that match a particular criteria are retrieved. • B. When you are filtering so that only columns that match a particular criteria are retrieved. • C. A and B • D. None of above • A. Qualified records will be returned.

  7. I.5 • Which command is used to remove a record from a table? • A. Drop Table • B. Alter Table • C. Delete from • D. Update • E. Drop column • C.

  8. I.6 • To student entity, which of following can be used as Primary key? • A. Date of Birth • B. SSN • C. Passport No. • D. Full Name • E. Parent’s Names • F. Driver License Number • B or BCF

  9. Greater Value Creation Business Value Creation BI Maturity Greater Maturity II. BI Maturity stage of Temple U. • Changing Information Usage Stage 3 Changed Information Usage Paradigms Across Enterprise to Leverage BI Information Fully Integrated into Business Stage 2 Changed Information Usage Paradigms in One or More Functions to Leverage BI Building and Using Information Assets Stage 1 No Change to Information Usage Paradigms Improved / Focused / Custom Reporting Stage 0 No DW / BI Experience Status Quo

  10. III.SQL (35 points total)

  11. Write the following SQL queries based on the basketball player database

  12. III.1 • (6 points) Assume the college table is already there. Please write code to create the player table with all attributes. You also need to add the primary key and foreign key to player table. • CREATE TABLE player • ( • Player_IDintNOT NULL PRIMARY KEY, • Fnamevarchar(255), • Lnamevarchar(255), • Jersey number int, • College int NOT NULL, • Year_graduationint, • DOB date, • GPA float, • FOREIGN KEY(College) REFERENCES college(College_ID) • )

  13. III.2 • Show all the records in the basketball player table. Order the results by descending Lname then descending Fname. • SELECT * • FROM player • ORDER BY LnameDesc, FnameDesc

  14. III.3 • Show the unique cities of all colleges (each city should display just once in the results). • SELECT DISTINCT College_city • FROM college

  15. III.4 • Show the player’s first and last name, college, GPA if the college ID is 1 and the student’s GPA is greater than 3.5, or if the college ID is 2. • SELECT Fname, Lname, College, GPA • FROM player • WHERE (College=1 AND GPA>3.5) OR (College=2)

  16. III.5 • Display the player’s first and last name, college, GPA if the college is 1, 2, 3, 4, or 5. Order the output by player’s last name. • SELECT player.Fname, player.Lname, college.College_name, player.GPA • FROM player, college • WHERE (player.College=college.ID) • AND player.College>=1 AND player.College<=5 • ORDER BY player.Lname • Other options for college ID specification: • Player.College IN (‘1’, ‘2’, ‘3’, ‘4’, ‘5’) • Player.College BETWEEN ‘1’ AND ‘5’

  17. III.6 • Show the player’s first and last name if the player’s last name starts with ‘S’. • SELECT Fname, Lname • FROM player • WHERE Lname LIKE ‘S%’

  18. IV. Hotel ERD (35 points)

  19. Step 1. Entities, primary keys, and intrinsic attributes Going to connect all to Invoice!

  20. Step 2. Relationships? • Guest::Invoice = ?::? • Inn:: Invoice = ?::? • Service:: Invoice = ?::?

  21. Step 2 Continued • Guest::Invoice = ?::? • 1 guest is associated with many invoice (1:M) • 1 invoice is associated with only 1 guest (1:1) • After all, Guest::Invoice = 1::M

  22. Step 2 Continued • Inn:: Invoice = ?::? • 1 Inn is associated with many invoice (1:M) • 1 invoice is associated with only 1 Inn (1:1) • After all, Inn::Invoice = 1::M

  23. Step 2 Continued • Service:: Invoice = ?::? • 1 service is associated with many invoice (1:M) • 1 invoice is associated with many services (1:M) • After all, Service::Invoice = M::N

  24. Step 3. Solve M::N issue 1::M M::N 1::M M::N will introduce redundancy. We need a bridge entity here!

  25. Step 3: Solve M::N, add bridge entity 1::M 1::M M::1 1::M Next, Q1: How many foreign keys are needed? Q2: Which sides to add foreign keys? “M” or “1”?

  26. Step 4: Add foreign keys to M sides 1::M 1::M M::1 1::M 4 foreign keys are added to M sides

  27. Step 5. Connect PK to FK DONE!

More Related