Database Management Systems



Database Management SystemsSept. 28, 2012Name__________________________Match the best of the three schema levels (E=external, P=physical and C=conceptual) in a database system architecture to each of the characteristics. [10 pts]____-- An Entity-Relationship model helps visualize this schema level.____-- The layout of the data within the storage file system is defined in this schema level.____-- There is exactly one schema at this level detailing all data attributes, their types and constraints.____-- The schema at this level is typically generated automatically from the conceptual schema.____-- A simplified view of the database is often provided at this level for the end user.____-- Access to some of the database tables or attributes can be limited by this schema level.____-- There can be multiple schemas at this level.____-- The table indexing schemes for improved performance are found at this level.____ and ___-- The mappings between these levels provide for program-data independence.True/False on database approaches. [6 pts]_____ The relational model was the earliest major database approach._____ The hierarchical model represents the database schema as a tree._____ A relational model database is most closely related to mathematical set theory._____ An object oriented database model is akin to a hierarchical database model._____ A network database approach is the most efficient in use of storage and best in performance._____ An object oriented database is today’s primary database approach.Define each of these terms and give an example to support the definition.[12 pts]Database persistence:Database administrator:Integrity constraint:Multivalued attribute: True/false on functional dependency theory.[10 pts]______ Functional dependency analysis aids in the normalization of a relational schema.______ Multiple entries of a fact is at the root of insertion and deletion anomalies in a relational database.______ Normalization attempts to retain redundant facts stored in a database.______ A null value is the empty string or zero.______ A functional dependency is a relationship between an attribute "Y" and a determinant “X” (1 or more other attributes) such that for a given value of X the value of the attribute Y is uniquely defined.______ Functional dependencies can be determined by algorithms to analyze the relation.______ In practical terms, we want other all non-key attributes to functionally determine the primary key in any relation.______ Functional dependencies are reflexive, i.e., if a →b then b →a______ Transitivity of functional dependencies means that if b →c and a→b then a→c.______ Decomposition of functional dependencies means that if a→bc then a→b and b→cFor parts a-c, assume we have a relation with the scheme TheatreProductions(Title, ShowDateTime, Writer, Director, CopyrightYear, Attendance )[12 pts] What would be the likely primary key attribute(s)? _________________________________[2]List all non-trivial functional dependencies [7]?If this relation were used as defined (not normalized), describe the insertion and deletion anomalies that could arise. [3]For the following ER diagram develop the relational schema using the pattern TableName(attribute-list). Underline the key attribute(s) in each relation. In the interest of time and space, just list 3 more non-key attributes in your table from the entities, not all attributes are necessary.[15 pts]For the remaining questions, use the following relational schema for a rental company database. Keys are (mostly) underlined. The attributes should be self-evident. If not, please ask for clarification. Customers may rent several items at a time and they are all listed under one invoice. Payment is due upon return of the items. Credit cards are used in lieu of a deposit and the hold is released on return of the items, but payment can be by check as well.CUSTOMER(custID, name, address, zip, phone, custType)CSZ(zip, city, state)INVOICE(invID, custID, dateOfInv, finalTotalBill, amountDue, creditCardNo)ITEMSONHAND(typeID, description, quantityOnHand, totalOwned, dailyRentRate)ITEMDESCR(itemID, typeID, condition, dateBought, purchasePrice)RENTED(invID, itemID, dateReturned)PAYMENT(invID, type, CrCardOrCheckNo, amount, date)[10 pts]a) Underline the appropriate attribute(s) of the primary key for the relation RENTED. b) Circle attributes that could be virtual in the INVOICE relation.c) Draw arrows from foreign keys to their corresponding table and attribute d).What candidate keys are there in the CUSTOMER relation?_________________________________________Give Relational Algebra statements for the following queries on the rental company database schema. Use the R.A. notation below. BE EXPLICIT in the join condition what attributes make the join where necessary.[25 pts]Syntax reminder for Relational Algebra expressions:SELECT?: condition(relation)PROJECT?: attribute-list(relation)SET Operations and JOIN: relation1 OP relation2, where OP is , , - , , , and ||conditionRENAME: relation[new attribute names]ASSIGN: new-relation(attrs) R.A. expression a) List all names and phone numbers of ‘contractor’ customer type.List names of customers from zip 16652 who have had invoices of at least $500.The pig roasters (id=PIGRSTR) bought before 2005 were discovered to contain asbestos. List names and phone numbers of customers who have rented one of these items.List customer names who have not rented anything since Jan 1 2010. List all customer IDs who have made at least two payments for the same invoice. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download