Database Management Systems - Juniata College



Database Management SystemsOct. 4, 2019Name__________Key___________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, Network db.[15 pts]__Entity_ A real life object._Relationship_ A real life connection between two real life objects._Transaction_ When a series of steps must complete entirely or act as if none at all.__OLAP__ Processing of the data in the database for analytical purposes.__Persistence_ That data is ensured to be retained in the database system._Derived Attr_ An attribute computable from other data within the database.___Role__ A named purpose in a relationship between entities.__IsaHier_ Complex entity structure to represent simple differences in attributes and shared attributes.__CandKey__ An attribute set that can be used to uniquely identify a tuple.__Cardinality__ How many other objects can be related to a given object in a relationship.__Domain_ The legal set of values for an attribute.__BigData_ Defined by information value, large volume, high velocity and wide variety._WeakEntity_ An object that can exist only when related to another existing object._Integrity_ Ensuring that data follows a given set of constraints.__ForeignKey__ An attribute that refers to a primary key.True/False. [20 pts]__T__ The entity-relationship (ER) model is mapped to the conceptual level. __F__ Simplifying and/or hiding some of the database tables for the end users occur at the internal level. __T__ There can be many views of the database found at the external level. __T__ Tables and data records are structured and implemented for storage at the internal level. __T__ There is only one instance of the model at the logical/conceptual level. __F__ The operating system interacts with the database management system at the external level.__T__ An application written in Python or Java accesses the database through the external level.__F__ The relational 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 hierarchical database is expected to remain the primary approach in foreseeable future.__T__ A functional dependency X→Y is an extension of the mathematics concept that a value in X functionally a value in Y.__F__ Functional dependencies can be determined sufficiently by analyzing a sampling of a relation.__T__ Functional dependencies are reflexive, i.e., a →a holds.__F__ Transitivity of functional dependencies means that if a →b and a→c then b→c.__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.Books(ISBN, Title, CopyrightYear, Author, Publisher, PublisherURL, AuthorEmail )[17 pts] Specify an appropriate primary key. ___ISBN_________________[2]Give another candidate key: ____Title,Author[,CRyear]____________[2]List all non-trivial functional dependencies. Be sure to identify all determinants. [9]ISBN -> Title, CRYr, Author, Publ, PubURL, AuthorEmailTitle, CRYr, Author -> ISBN, Publ, PubURL, AuthorEmail[Publisher -> PubURL]PubURL -> PublisherAuthorEmail -> Author //author names (primary) not guaranteed to be uniqueAs given, describe two problems with this books relation. [4]Publisher info should be a separate tableMultiple authors possibleMultiple authorEmails possibleFor the following Presidents ER diagram define, as directed, the four relations using the table pattern syntax TableName(attribute-list). Define the tables for the entity Admins, the two entities directly relating through Admins, and any tables arising from the connecting relationships or multivalued attributes. [18 pts]Underline your primary key attribute(s) in each relation. Be sure to include the foreign keys here too and draw a line connecting the FKs to the PKs.#1. Admins: Admins(AdminNo, Pres, InaugDate)137160026219159853126220#2 :AdminVPs(AdminNo, VP)1511764376231097947170889#3 : Presidents(Pres,Party,State,Spouse,BDate, DDate)#4 : States(State,Capital, Pop, Votes, AdminNo)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.EmpS (SSN, name, address, zip, phone, deptno, jobTitle, salary)CSZ (zip, city, state)DEPTS (deptno, deptName, deptMgrSSN)TRIPS (TripId, DestinationCity, DepartureDate, ReturnDate,?SSN)EXPENSES (TripId, Item,?Date, Amount)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.[30 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(new attr names) R.A. expression a) List all names and job titles of employees.name,jobTitle (EMPS)b) List all names and zip codes of people who earn more than $50000 in salary.name,zipsalary>50000(EMPS)c) List employee names and job titles of those who are currently traveling. You can use the function call NOW() to refer to today. And assume ReturnDates can be in the future.name,jobTitlereturnDate>Now() and DepartureDate<Now()(EMPS |X| SSN TRIPS)EmpS (SSN, name, address, zip, phone, deptno, jobTitle, salary)CSZ (zip, city, state)DEPTS (deptno, deptName, deptMgrSSN)TRIPS (TripId, DestinationCity, DepartureDate, ReturnDate,?SSN)EXPENSES (TripId, Item,?Date, Amount)d) List all the expense items, dates, amounts and destination city for employee John Dough.item,date,amountname = ‘John Dough’ (EMPS |X| SSN TRIPS |X|TripId EXPENSES)e) List employee names from the Accounting department who have not traveled (Hint: do a join after set difference). name deptName = ‘Accounting’ DEPTS |X| EMPS |X| (SSN EMPS - SSN TRIPS) f) List employee names who have traveled to Paris at least twice. (Hint: self-join)nameTripId<> TripId1 and DestinationCity=’Paris’ (EMPS |X| SSN (TRIPS |X|SSN,DC TRIPS [Id1,DesignationCity,DD1,RD1,SSN)) ................
................

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

Google Online Preview   Download