Database Management Systems - Juniata College



Database Management SystemsSept. 28, 2018Name_______KEY______________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]___L____ The entity-relationship (ER) model is mapped to this level. ___E____ Simplifying and/or hiding portions of the data base tables for the end users occur at this level. ___ E ____ There can be many views of the database found at this level. ___ P ____ Tables and data records are structured and implemented for storage at this level. __ L _ P ___ There is only one instance of the model at this level. ____ P ___ The operating system interacts with the database management system at this level.____ E ___ An application written in Python or Java accesses the database through this level.___ L ____ 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]_Cand.Key_ Attribute set that can be used to uniquely identify a tuple._Relnshp_ A real life connection between two real life objects.__Entity_ A real life object_Cardnty_ Whether a real life object can be related to multiple other, but different, real life objects._Domain__ Set of values that an attribute can draw upon.__BigData_ Defined by value, volume, velocity and variety_WeakEnt__ An object that can exist only when related to another existing object.__Integrity_ Insurance that data follows a number of constraints__For.Key_ An attribute that refers to primary key_Transact._ When a series of steps must complete entirely or act as if not at all.___OLAP___ Processing of the database for analytical purposes__Persistence_ That data is ensured to be found in the database system.__DerivedAtt_ An attribute computable from within the database__Role____ An entity that has a named purpose in a relationship__IsAHier___ Complex entity structure to represent simple differences in attributes and share attributes.True/False on database approaches. [6 pts]__F__ The network model was the earliest major database approach.__T__ The hierarchical model represents the database schema as trees.__ F __ A network modeled database is most closely related to mathematical set theory.__ F __ An object oriented database model has similarities to a hierarchical database model.__ F __ The primary appeal of a relational database in early days was its amazing efficiency.__ F __ The object-oriented database is expected to remain the primary approach in foreseeable future.True/false on functional dependency theory.[7 pts]__ T ___ A functional dependency X→Y is an extension of the mathematics concept that a value X is related to a set of values Y.__ F ___ Functional dependencies can be determined sufficiently by analyzing a sampling of a relation.__ T ___ Functional dependencies are not reflexive, i.e., if a →b then b →a is false.__ F ___ Transitivity of functional dependencies means that if a →b and b→c then c→a.__ F ___ It’s ok to rewrite ab→cd as a→c and b→d, and vice versa.__ T ___ 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.__ F ___ 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. _____ShowDateTime opt w/ Title______________[2]List all non-trivial and non-transitive functional dependencies. [8]Title -> Writer, CopyrightYear, (Title,) ShowDateTime -> Writer, Director, CopyrightYear, AttendanceDirector -> DirectorPhone As given, describe two problems with this theater productions relation. [4]Cannot store basic facts about writers, directors, and shows without a show date, time and attendance, of vice versa 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:States (State, Pop, Capital, Votes, AdminNo)ElectionLosers: ELECTIONLOSERS (Party, Year, Name )VPs: AdminsVP( AdminNo, VP)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? ____ YTDPaid _____c) 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 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.name, phone priorzip=’90210’(TENANTS)List all names of those who lived in ‘PA’ prior to renting. [be sure to clarify any join connection attributes]name state=’PA’(TENANTS |X|Priorzip=zip CSZ)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.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 those 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 at any point in time. (Hint: self-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