Database Management Systems



Database Management SystemsOct. 3, 2014Name__________________________Consider a simplified three level database system architecture of external, internal and conceptual levels. Fill in the blanks to accurately describe the connection of, and activities at, each level. [8 pts]Entity-relationship modeling is at the ____________ level. Conversion of the ER model to the relational model is found at the ________________ level. Specifying relationships and their cardinality occurs at the ____________level. How the tables and data records are structured for storage are defined in the ________________ level. Simplifying and hiding the data base tables for the end users occurs at the ________________ level. There is/are _______ [choose one or many] view(s) found at the external level. There is/are _____ instance(s) of the model(s) at the logical level. The database system interface between the internal and logical model provides for internal-logical data ________________ so that changes to one level has minimal effect on the other.True/False on database approaches. [6 pts]_____ The object oriented 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 has similarities to a network database model._____ A relational database in early days were considered too inefficient for practical use._____ An object oriented database is expected to become the primary database approach in five years.Contrast each of these pairs of terms [12 pts]Database transaction Vs database operation:Cardinality of a relationship Vs cardinality of a relationWeak entity Vs a stong entity: True/false on functional dependency theory.[10 pts]______ Functional dependency analysis is required in the normalization of a relational schema, but is irrelevant in the analysis of an entity’s attributes in the ER model.______ Multi-valued attributes are permitted in a normalized relational database.______ Normalization also permits nulls to be stored in database tables.______ A null value is equivalent to zero in the case of a number type attribute.______ A functional dependency of Y on X is an extension of the mathematics concept that a value in X clearly determines the corresponding value in Y.______ Functional dependencies can be determined by algorithms to analyze the relation’s attributes.______ In practical terms, we want other all non-key attributes to be functionally dependent only on the candidate keys in the relation.______ Functional dependencies are reflexive, i.e., if a →b then b →a______ Transitivity of functional dependencies means that if a →b and c→a then b→c.______ It’s ok to rewrite a→bc as a→b and b→c, and vice versa.For parts a-c, assume we have a relation with the schema Persons (SSN, Name, StreetAddr, City, State, Zip, Phone, Employer, EmpAddress )[14 pts] What would be the likely primary key attribute(s)? _________________________________[2]List all non-trivial functional dependencies [9]?If this relation were used as defined (not normalized), describe an update anomaly that could arise. [3]For the following car repair shop ER diagram develop the relational schema using the pattern TableName(attribute-list). This is a different notation without diamonds for relationships. Connected entities are related and the crow’s feet are the “many” side of the relationship. Ignore the second “Date” attribute. Underline the key attribute(s) in each relation. Be sure to include the foreign key.[18 pts]For the remaining questions, use the following relational schema for an apartment rental company. They track tenants or potential tenants. Apartments have a unique id and are spread around in several towns and have different rental rates. They track the current leasing of an apartment to a tenant as well as past leases to each apartment and tenant. Let me know if you have any other clarifications.TENANTS (PID, name, PriorAddress, Priorzip, phone)CSZ (zip, city, state)APTS (AptID, AptNo, AptAddress, AptZip, bdrms, Monthrent) RENTED (PID, AptId, StartDate, EndDate, deposit, YTDPaid) //past renters are kept and if EndDate is null, the apartment is currently rented.[7 pts]a) Underline the appropriate attribute(s) of the primary key for the relation RENTED. b) What attribute could be virtual in the RENTED relation?c) Draw arrows from foreign keys to their corresponding relation and attribute.d) What candidate keys are there in the TENANTS 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 which 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 people from those who lived at zip 90210 prior to renting.List all names of those who lived in ‘PA’ prior to renting. [be sure to clarify any connection attributes]List names and phones of past renters (no longer renting) who had monthly payments greater than $1500.Get a list of apartment numbers and addresses that are not currently rented. (Hint: First establish a list of aptids representing the not rented-- a set operation is very helpful here, then re-join the result for the rest of the information) List names of renters who have rented two different apartments. (Hint: self-join) ................
................

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

Google Online Preview   Download