Transcript 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?
– A. Business intelligence refers to a bunch of computerbased 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.
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:
–
–
–
–
–
• B.
A. Check constraint
B. Default constraint
C. Cascade constraint
D. Define constraint
E. None of the above
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
I.4
• When do you need a where clause 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.
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.
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
II. BI Maturity stage of Temple U.
• Changing Information Usage
Greater
Value
Creation
Information Fully
Integrated into
Business
Business
Value
Creation
Building and
Using Information Assets
Stage 2
Changed Information Usage
Paradigms in One or More
Functions to Leverage BI
Stage 1
No Change to Information
Usage Paradigms
Stage 0
No DW / BI Experience
Stage 3
Changed Information Usage
Paradigms
Across Enterprise
to Leverage BI
Improved /
Focused / Custom Reporting
Status Quo
BI Maturity
Greater Maturity
III.SQL
(35 points total)
Write the following SQL queries based
on the basketball player database
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_ID int NOT NULL PRIMARY KEY,
Fname varchar(255),
Lname varchar(255),
Jersey number int,
College int NOT NULL,
Year_graduation int,
DOB date,
GPA float,
FOREIGN KEY(College) REFERENCES college(College_ID)
)
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 Lname Desc, Fname Desc
III.3
• Show the unique cities of all colleges (each
city should display just once in the results).
– SELECT DISTINCT College_city
– FROM college
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)
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’
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%’
IV. Hotel ERD
(35 points)
Step 1. Entities, primary keys, and
intrinsic attributes
Going to connect all to Invoice!
Step 2. Relationships?
• Guest::Invoice = ?::?
• Inn:: Invoice = ?::?
• Service:: Invoice = ?::?
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
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
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
Step 3. Solve M::N issue
1::M
M::N
1::M
M::N will introduce redundancy.
We need a bridge entity here!
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”?
Step 4: Add foreign keys to M sides
1::M
1::M
M::1
1::M
4 foreign keys are added to M sides
Step 5. Connect PK to FK
DONE!