Database Management Systems



Database Management SystemsSept. 28, 2018Name__________________________Consider the simplified three level database system architecture of external (E), internal/physical (P) and conceptual/logical (L) levels. List the level (E,P,L) that best matches the description. [8 pts]________ The entity-relationship (ER) model is mapped to this level. ________ Simplifying and/or hiding portions of the data base tables for the end users occur at this level. ________ There can be many views of the database found at this level. ________ Tables and data records are structured and implemented for storage at this level. ________ There is only one instance of the model at this level. ________ The operating system interacts with the database management system at this level.________ An application written in Python or Java accesses the database through this level.________ This is the middle level. Match one of the following concepts to each definition or example below. Transaction, Entity, Weak Entity, Cardinality, Relational Algebra, Derived Attribute, Composite Attribute, Integrity, Relationship. Candidate Key, Foreign Key, Domain, Isa Hierarchy, Big Data, Persistence, OLAP, Role[15 pts]_________ Attribute set that can be used to uniquely identify a tuple._________ A real life connection between two real life objects._________ A real life object_________ Whether a real life object can be related to multiple other, but different, real life objects._________ Set of values that an attribute can draw upon._________ Defined by value, volume, velocity and variety_________ An object that can exist only when related to another existing object._________ Insurance that data follows a number of constraints_________ An attribute that refers to primary key_________ When a series of steps must complete entirely or act as if not at all._________ Processing of the database for analytical purposes_________ That data is ensured to be found in the database system._________ An attribute computable from within the database_________ An entity that has a named purpose in a relationship_________ Complex entity structure to represent simple differences in attributes and share attributes.True/False on database approaches. [6 pts]_____ The network model was the earliest major database approach._____ The hierarchical model represents the database schema as trees._____ A network modeled database is most closely related to mathematical set theory._____ An object oriented database model has similarities to a hierarchical database model._____ The primary appeal of a relational database in early days was its amazing efficiency._____ The object-oriented database is expected to remain the primary approach in foreseeable future.True/false on functional dependency theory.[7 pts]______ A functional dependency X→Y is an extension of the mathematics concept that a value X is related to a set of values Y.______ Functional dependencies can be determined sufficiently by analyzing a sampling of a relation.______ Functional dependencies are not reflexive, i.e., if a →b then b →a is false.______ Transitivity of functional dependencies means that if a →b and b→c then c→a.______ It’s ok to rewrite ab→cd as a→c and b→d, and vice versa.______ Functional dependency analysis is core in the normalization process of a relational schema, but is just as useful in the analysis of an entity’s attributes in the ER model.______ A null value is just the empty string in the case of a non-numeric type attribute.For parts a-c, assume we have a relation with the following schema.TheatreProductions(Title, ShowDateTime, Writer, Director, CopyrightYear, DirectorPhone, Attendance )[14 pts] Specify an appropriate primary key. _________________________________[2]List all non-trivial and non-transitive functional dependencies. [8]As given, describe two problems with this theater productions relation. [4]For the following Presidents ER diagram define, as directed, the three relations using the table pattern TableName(attribute-list). Define the relation for the entity States, ensuring that you account for the relationships it participates in. Define the relation ElectionLosers representing that relationship. Define a vice president (VPs) relation to handle the multi-valued attribute in Admins. Underline the key attribute(s) in each relation. Be sure to include the foreign key.[15 pts]States:ElectionLosers:VPs: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 past tenants. Keys are (mostly) underlined. The attributes should be self-evident. If not, please ask for clarification.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’ data are kept and if EndDate is null, the apartment is currently rented.[10 pts]a) Underline the appropriate attribute(s) for the primary key of the relation RENTED. b) What attribute could be virtual/derived 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 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 |X|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 join connection attributes]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’ data are kept and if EndDate is null, the apartment is currently rentedList 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 those 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 at any point in time. (Hint: self-join) ................
................

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

Google Online Preview   Download