MCIS 6203 – Midterm | Complete Solution

MCIS 6203 – Midterm

Your Name
NOTES: Attach additional pages with your name in the upper right corner, if needed.
Provide your answer for each question in the space provided.
Multiple choice questions. Each question is worth 2 points. Select correct answer.
______1. What is true about Databases?
a) Large repository of data
b) Shared resource, used by many departments and applications
c) Contains several different record types
d) “knows” about relationships in data
e) All of the above.
______2. Who is responsible for managing Databases?
a) Databases are self managing
b) Programmer
c) Database administrator
d) Vendor
______3. Every DBMS uses a data sublanguage, which has two parts
a) C and C++
b) SQL and DQL
c) DDL and DML
d) DQL and DCL
______4. The process of restoring the database to a correct state after a failure is called
a) rollback
b) recovery
c) restoration
d) correction
______5. A logical unit of work on the database is a(n)
a) program
b) retrieval
c) update
d) transaction
______6. An E-R model is
a) conceptual level model
b) logical level model
c) physical model
d) transactional model
______7. An EER model is
a) not needed since ER model is popular
b) used for advanced application design when completeness and accuracy is needed.
c) used for traditional application.
d) used by DBA to monitor database performance.
______8. An EER model is good choice for applications like
Dr. Raj Singh 2
a) Geographical information systems, search engines, data mining, etc.
b) Non-complex small scale
c) Web applications with static pages
d) Integrated circuit design
______9. All of the following are SQL DDL commands except
a) CREATE TABLE
b) DROP INDEX
c) ADD TABLE
d) ALTER TABLE
______10. The specification in the SQL CREATE TABLE command that enforces referential integrity is the
a) PRIMARY KEY
b) UNIQUE INDEX
c) FOREIGN KEY
d) NOT NULL
______11. An SQL index specification that causes records with the same values on the indexed field to be stored close together is
a) NOT NULL
b) UNIQUE
c) CLUSTER
d) DISTINCT
______12. In standard SQL, all of the following are valid ON DELETE options EXCEPT
a) CASCADE
b) SET NULL
c) CHECK
d) NO ACTION
______13. The default specification for ON UPDATE on foreign keys is
a) CASCADE
b) SET NULL
c) CHECK
d) NO ACTION
______14. Oracle automatically creates indexes for
a) the primary key of each table
b) all foreign keys
c) both primay keys and foreign keys
d) all unique attributes
______15. Most relational DBMSs use what structure to store indexes?
a) linked lists
b) hashed files
c) B+ trees
d) red-black trees
______16. When you add a column to an existing table by using an SQL ALTER TABLE command, the column cannot contain the specification
Dr. Raj Singh 3
a) WITH DEFAULT
b) CHECK
c) NOT NULL
d) UNIQUE
______17. The ALTER TABLE command can be used to do all of the following except
a) add new columns
b) drop columns
c) rename the table
d) add a constraint
______18. Object oriented data model is needed because
a) Traditional relational model does not represent complex data and relationships well
b) Need additional support for advanced applications
c) OO paradigm widely used for programming
d) OO database provides persistent objects to correspond to temporary objects in programs
e) All of the above
______19. PL/SQL is all of the following except
a) An extension to SQL.
b) Prompt logic
c) Procedural language – SQL
d) design features of programming languages (procedural and object oriented)
______20. All of these are advantages of PL/SQL except
a) Acts as host language for stored procedures and triggers.
b) Provides the ability to add middle tier business logic to client/server applications.
c) Restricts portability of code to one environment
d) Improves performance of multi-query transactions and provides error handling
Answer the following questions.
21. Below are the following stages associated with the database lifecycle.
Database stages: Planning, Systems definition, Requirements, Conceptual design, logical design, physical design, Implementation, Data conversion, Testing, and Operational maintenance
During which phase would the following event most likely occur? (10 points)
A) Actual construction of E-R diagrams _______________
B) Consider cost/benefit analysis of migrating to a web-based database _______________
C) Determine which part(s) of an organization will be impacted by _______________
proposed database
D) Examine transaction throughput _______________
E) Interview senior management _______________
F) Map the current Microsoft Access database to the new SQL database _______________
Dr. Raj Singh 4
G) Monitor the performance of the database _______________
H) Review integrity constraints _______________
I) Run sample transactions against the database to determine limitations _______________
J) Create schema, columns, tables etc.. _______________
22. What is (min..max) notation for relationships? (5 points)
23. What is generalization & specialization in EER model? Provide definition and example. (10 points)
Dr. Raj Singh 5
24. Explain the following terms in one sentence. (10 points)
1. entity ___________________________________________________________
2. class ___________________________________________________________
3. cursors ___________________________________________________________
4. attributes ___________________________________________________________
5. super key ___________________________________________________________
6. candidate key ___________________________________________________________
7. primary key ___________________________________________________________
8. relationships ___________________________________________________________
9. degree ___________________________________________________________
10. cardinality ___________________________________________________________
25. Given the following employees table, answer the following questions.
Clear Lake Technologies
Last Name
First Name
Date of Employment
Hourly Wage
Title
City
Jones
Ed
01/02/00
30
Mgr.
Houston
Smith
Bob
03/22/74
100
Pres.
Dallas
Spade
Susan
12/12/88
35
VP
Houston
Chi
Ti
06/09/93
22
Clerk
Houston
Page
Ann
05/12/95
22
Asst.
Houston
Tell
Willy
11/11/99
20
Clerk
Dallas
A) How many tuples are there in this relation? (5 point)
B) In the table below check those boxes that apply to the table above. ( 10 points)
Note: It is possible to check both or neither for any given row.
Field Description
Superkey
Candidate Key
Last Name
First Name
Last Name and First Name
Last Name, First Name, and Date of Employment
Last Name and Hourly Wage
Dr. Raj Singh 6
C) Provide SQL query for the followings: (10 points)
a. Find the names of the employees who are “Clerk” and from “Houston”
b. Find all the employees with hourly wage greater than 30. How many tuples are returned?
c. Willy Tell is promoted with 20% increase in wage to Supervisor and moved to Austin. Write an update query to reflect the changes.
d. Jack Knowsit is hired on 10/10/2010 as a Clerk to take place of Willy Tell at Dallas office with Hourly Wage $20. Insert a row in above table to add Jack in database.
e. Ti Chi resigned. Remove Ti from database.