数据库系统试卷及答案

Principles of Database Systems(A)(final test)— 0201~0203-Computer Science & TechnologyName________________ ID____________________I.Multiple Choice( 2 points * 20)1.Which is NOT a component of a relational database?a)Entityb)Tablec)Attributed)Hierarchy2.Which is NOT an advantage of the database approach?a)Limited data sharingb)Minimal data redundancyc)Improved data qualityd)Reduced program maintenance3.A formal, top-down methodology that uses a data orientation to create and maintaininformation systems.a)Enterprise data modelingb)Information engineeringc)Information system architectured)Strategic planning factors4.In this step of the SDLC, one writes, tests, and installs the programs that process the database.a)Database implementationb)Physical database design and definitionc)Database maintenanced)Logical database design5.Which is NOT an example of a strong entity type?a)STUDENTb)COURSEc)DEPARTMENTd)STUDENT_ID6.A property or characteristic of an entity type is a(n)a)attribute.b)relationship.c)field.d)trait.7.An attribute whose value can be calculated from related attribute values is a(n) ___ attribute.a)compositeb)multivaluedc)storedd)derived8.The most common type of relationship encountered in data modeling is the ____ relationship.a)unaryb)binaryc)ternaryd)associative9.The process of defining a more basic entity type from a set of specialized entity types iscalleda)generalization.b)specialization.c)inheritance.d)subtype hierarchy.10.The ____ specifies that an entity instance can simultaneously be a member of two or moresubtypes.a)disjoint ruleb)disjoint constraintc)completeness constraintd)overlap rule11.In ____ normal form, any transitive dependencies have been removed.a)firstb)secondc)thirdd)fourth12.The ____ guarantees that every primary key attribute is not null.a)entity integrity ruleb)referential integrity constraintc)action assertiond)composite attribute13.A(n) ____ is a constraint between two attributes.a)determinantb)action assertionc)candidated)functional dependency14.Data integrity controls may includea)default values, null value control, and denormalization.b)default values, hashing, and referential integrity.c)range control, hashing, and denormalization.d)range control, referential integrity, and default value.15.A ____ is a routine that converts a primary key value into a relative record number.a)hashing algorithmb)hash index tablec)join indexd)hierarchical index16.Which is NOT an advantage of using a view?a)Simplify query commandsb)Provide data securityc)Enhance programming productivityd)Decrease system overhead17.With ____, each transaction is restricted to a view of the database as of the time thattransaction started.a)deadlockb)versioningc)shared locksd)exclusive locks18.____ means that the database constraints that must be true before the transaction must be trueafter the transaction.a)Atomicb)Consistentc)Isolatedd)Durable19.Which is NOT one of the basic facilities for backup and recovery of a database?a)Checkpoint facilityb)Recovery Managerc)Biometric Deviced)Journalizing facilities.20.Before- and after-images of records that have been modified by transactions are in a ____.a)database change logb)transaction logc)checkpointd)journalizing facilityII.Essay Questions(15points)1.Draw an EER diagram for the following narrative.A hospital PA TIENT has two subtypes: OUTPA TIENT and RESIDENTPA TIENT.Following are the attributes for each type of patient:OUTPA TIENT: Patient_ID, Patient_Name, Admit_Date, Checkback_DateRESIDENTPA TIENT: Patient_ID, Patient_Name, Admit_Date, Date_Dischargedevery patient is cared for by a RESPONSIBLE PHYSICIAN which has an attribute Physician_ID, each physician may care for zero to many patients.resident patients have a relationship that assigns each patient to a BED which has an attribute Bed_ID(notice that this is a mandatory relationship). Each bed may or may not be assigned toa patient.assume that each patient must be a member of exactly one of these subtypes. Remember to include a subtype discriminator and cardinality. And underline the primary key.Solution:(8points)2. For the following ER diagramPlease develop a relational schema( similar to the following figure)Solution:Foreig n keyForeig n keyComposite primary key(15points)3. For the following relation, pleasea) find all the candidate keysb) .for every candidate key that you selected as Primary Key, indicate which normal form the relation is in, respectively.c)decompose the relation into BCNF.R(A, B, C, D , AB →C, AB →D, C →B )Solution:a) AB or ACb) If AB is PK, there is no partial FDs or transitive FDs, so the relation is in 3NF, but not in BCNF, because C is a determinant but not a candidate key. if AC is PK, there is partial FDs, so the relation is in 1NF. c) R1(A,B,D, AB →D)R2(C,B, C →B)orR1(A,C,D, AC →D) R2(C,B, C →B)(15points)4. For the following relation, write SQL retrieval commands for each of the following queries:1) for each customer who has placed an order, what is the customer ’s name and order number? 2) which customers have not placed any orders?3) give the equivalent SQL command for the following relational algebra expression ))()_((ORDER_Pr _Pr ,_PRODUCT LINE ORDER IDoductIDoduct ID Order∏∏÷solution:1)SELECT Customer_Name, Order_IDFROM CUSTOMER INNER JOIN ORDERON CUSTOMER.Customer_ID=ORDER.Customer_IDorSELECT Customer_Name, Order_IDFROM CUSTOMER, ORDERWHERE CUSTOMER.Customer_ID=ORDER.Customer_ID2)SELECT * FROM CUSTOMERWHERE Customer_ID NOT IN (SELECT Customer_ID FROM ORDER)3)SELECT * FROM ORDERWHERE NOT EXISTS (SELECT * FROM PRODUCTWHERE Product_ID NOT IN (SELECT Product_ID FROM ORDER_LINEWHERE ORDER_LINE.Order_ID=ORDER.Order_ID))orSELECT * FROM ORDERWHERE NOT EXISTS (SELECT * FROM PRODUCTWHERE NOT EXISTS (SELECT * FROM ORDER_LINEWHERE ORDER_LINE.Order_ID=ORDER.Order_IDAND ORDER_LINE.Product_ID=PRODUCT.Product_ID))(7points)5.For the following figure there is a problem of Lost Updates as no concurrency control, pleasedraw a figure using locking mechanisms to solve the problem.Solution:。