Multiple choice questions. Each question is worth 2 points. Select correct answe
ID: 3758065 • Letter: M
Question
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
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
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
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)
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
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.
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
Explanation / Answer
1: Databases are collection of large data which can be shared by organization among departments contains different type of data and also shows the relationship between different data. So all of the above is correct
2. Database administrator is the only person who is responsible for mainaining and managing database
3. Sql is the database langugae which means Structured Query Language which has 2 parts DDL(data defination language and DML(Data manumulation Language). SO DDL DML is correct
4. The process of restoring the database to a correct state after a failure is called Rollback. In rollBack database is replaced with the database at Last Commit.
5. Transaction is logical unit of work on databse. transaction is sequence of related commands that accomplish one task performed on database.
9. Alter Tablle- As alter table change the Data of table so It is a DML Command.
10. Referiential Integrity is a concept of Foreign Key.
12. Set Null
13. No Action
14. Primary Key
17. Rename Table. To rename a table SQl has direct Rename command
18. All of the above
25 a.Tuple is record/row in table . So 5 tuples in given table.
b.
Field Description
Superkey
Candidate Key
Last Name
First Name
Last Name and First Name
Y
Last Name, First Name, and Date of Employment
Y
Last Name and Hourly Wage
25 C .a Select first name from employee where city='Houston'
b. select * from employee where Hourley Wage >30
to calculate no of tuples.
select count(*) from employee where Hourley Wage >30
D. insert into employee values('knoqsit','Jack','10/10/2010','20','Clerk','Dallas');
E. delete from Employee where last name='Chi' and first name='Ti';
7: Enhanced Entity relationship diagram is an extension of ER Diagram used for advanced application design when completeness and accuracy is needed like engineering design and manufacturing (CAD/CAM), telecommunications, complex software systems and geographic information systems (GIS).
8. EER is used in Geographical information systems, search engines, data mining, etc.
11: An SQL index specification that causes records with the same values on the indexed field to be stored close together is NOT NULL
15: Most relational DBMSs use B+ trees to store indexes.
16. When you add a column to an existing table by using an SQL ALTER TABLE command, the column cannot contain the specification NOT NULL
19:PL/SQL is all of the following except prompt Logic.
20: Restricts portability of code to one environment.
22. Min-Max notation should be shown like (min,max) where first value defines minimum value and second defines maximum.
Examples:
A department has exactly one manager and an employee can manage at most one
department.
Specify (0,1) for participation of EMPLOYEE in MANAGES
Specify (1,1) for participation of DEPARTMENT in MANAGES
23. Generalization is a bottom-up approach in which two lower level entities combine to form a higher level entity. In generalization, the higher level entity can also combine with other lower level entity to make further higher level entity.
Specialization is opposite to Generalization. It is a top-down approach in which one higher level entity can be broken down into two lower level entity. In specialization, some higher level entities may not have lower-level entity sets at all.
24. An entity is a thing or object of importance about which data must be captured.Information about an entity is captured in the form of attributes and/or relationships. Here attributes means Coulums in table. Example Employee, House,Car
Class: Suppose We’ll build a sales database—it could be for any kind of business. To sell anything, we need customers, so a Customer will be our first class (entity) type. Class can be identified by its attributes and Properties.
Cursors : In gereneral SQL query returned one row at a time but if you want to perform operations on collection of rows then Cursor is used. Cursor is collection of rows where it creates a result set of rows and then you perform operation on row by row basis on that cursor.
attributes : Entity is made up of different attributes. Like Customer is entity and Customer ID, First Name, Last Name etc are atributes.
Super Key: An attribute or set of attributes that uniquely identifies a tuple within a relation
Candidate key: A super key such that no proper subset is a candidate key within the relation
Primary key: The candidate key that is selected to identify tuples uniquely within the relation, the candidate keys which are not selected as PKs are called "Alternate keys"
A relationship is any association, linkage, or connection between the entities that has optional name. It can be of many types like one to one, one to many, many to one etc. Entities enrolled in a relationship are called its participants. relationship in ER diagram is shown as Diamond.
Degree. This is the number of entities involved in the relationship and it is usually 2 (binary relationship). Unary and Higher degree are also there.
Cardinality of a Relation - Number of tuples in a relation.
Dear User only One Ques left for which I am not pretty sure. So I will post answer for that question shortly. Thanks.
Field Description
Superkey
Candidate Key
Last Name
First Name
Last Name and First Name
Y
Last Name, First Name, and Date of Employment
Y
Last Name and Hourly Wage
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.