Database Management Systems



Database Management SystemsOct. 3, 2014Name_________Key____________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 __conceptual__ level. Conversion of the ER model to the relational model is found at the ___ conceptual ____ level. Specifying relationships and their cardinality occurs at the _ conceptual _level. How the tables and data records are structured for storage are defined in the ___internal____ level. Simplifying and hiding the data base tables for the end users occurs at the _____external___ level. There is/are __many_ [choose one or many] view(s) found at the external level. There is/are __1__ 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 ___independence__ so that changes to one level has minimal effect on the other.True/False on database approaches. [6 pts]__F__ The object oriented model was the earliest major database approach.__T__ The hierarchical model represents the database schema as a tree.__ T __ A relational model database is most closely related to mathematical set theory.__ T __ An object oriented database model has similarities to a network database model.__ T _ A relational database in early days were considered too inefficient for practical use.__ F __ 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:A transaction is a logical sequence of operations that need to be considered as a unit; a database operation is a single step.Cardinality of a relationship Vs cardinality of a relationCardinality of a relationship is how many entities of one is related to entities of another (one-many or many-many), cardinality of a relation is the number of tuples there are in the relation.Weak entity Vs a stong entity:Weak entities rely on existence of an entity in the relationship. A strong one can stand alone as a separate fact with no reliance the existence of a related entity. True/false on functional dependency theory.[10 pts]__F___ 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.__F___ Multi-valued attributes are permitted in a normalized relational database.__ F ___ Normalization also permits nulls to be stored in database tables.__ F ___ A null value is equivalent to zero in the case of a number type attribute.___ T __ 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.__ F ___ Functional dependencies can be determined by algorithms to analyze the relation’s attributes.___ T __ In practical terms, we want other all non-key attributes to be functionally dependent only on the candidate keys in the relation.__ F ___ Functional dependencies are reflexive, i.e., if a →b then b →a___ F __ Transitivity of functional dependencies means that if a →b and c→a then b→c.__ F ___ 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)? _______SSN__________________________[2]List all non-trivial functional dependencies [9]?SSN -> Name, StreetAddr, City, State, Zip, Phone, Employer, EmpAddressPhone -> SSN, Name, StreetAddr, City, State, Zip, Employer, EmpAddress Name, StreetAddr, Zip ->SSN, City, State, Phone, Employer, EmpAddressZip -> City, StateEmployer -> EmpAddressIf this relation were used as defined (not normalized), describe an update anomaly that could arise. [3]If an employer address changes, has to be changed in all tuplesFor 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]Cars(VIN, CustName, CustNo)Technician (EmpId, EmpName)Repairs(RCode, Description)CarRepairs(VIN, Date, Time, Result, Rcode, EmpID)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? YTDPaidc) Draw arrows from foreign keys to their corresponding relation and attribute.Tenants(Priorzip) references CSZ(zip), as does APTS(aptZip) references CSZ(zip)RENTED(PID) references Tenants(PID) and RENTED(AptID) references APTS(AptID)d) What candidate keys are there in the TENANTS relation? ____PID, phone, {name, prioraddress,priorzip}_____________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.name, phone priorzip=’90210’(TENANTS)List all names and cities of those who lived in ‘PA’ prior to renting. [be sure to clarify any connection attributes]name, city state=’PA’(TENANTS |X|Priorzip=zip CSZ)List names and phones of past renters (no longer renting) who had monthly payments greater than $1500.name, phone enddate is not null AND monthrent>1500(TENANTS |X| RENTED |X| APTS)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)aptno, aptaddress (APTS |X| (aptid (APTS) - (aptidenddate is null (RENTED))) List names of renters who have rented two different apartments. (Hint: self-join and another join)name aptid!= aptid1 (RENTED |X| RENTED [PID,aptid1,sd1,ed1,dep, ytdp1]) |X| (TENANT) ................
................

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

Google Online Preview   Download