Database Management Systems



Database Management SystemsOct. 4, 2019Name__________________________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]_________ A real life object._________ A real life connection between two real life objects._________ When a series of steps must complete entirely or act as if none at all._________ Processing of the data in the database for analytical purposes._________ That data is ensured to be retained in the database system._________ An attribute computable from other data within the database._________ A named purpose in a relationship between entities._________ Complex entity structure to represent simple differences in attributes and shared attributes._________ An attribute set that can be used to uniquely identify a tuple._________ How many other objects can be related to a given object in a relationship._________ The legal set of values for an attribute._________ Defined by information value, large volume, high velocity and wide variety._________ An object that can exist only when related to another existing object._________ Ensuring that data follows a given set of constraints._________ An attribute that refers to a primary key.True/False. [20 pts]_____ The entity-relationship (ER) model is mapped to the conceptual level. _____ Simplifying and/or hiding some of the database tables for the end users occur at the internal level. _____ There can be many views of the database found at the external level. _____ Tables and data records are structured and implemented for storage at the internal level. _____ There is only one instance of the model at the logical/conceptual level. _____ The operating system interacts with the database management system at the external level._____ An application written in Python or Java accesses the database through the external level._____ The relational 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 hierarchical database is expected to remain the primary approach in foreseeable future.______ A functional dependency X→Y is an extension of the mathematics concept that a value in X functionally a value in Y.______ Functional dependencies can be determined sufficiently by analyzing a sampling of a relation.______ Functional dependencies are reflexive, i.e., a →a holds.______ Transitivity of functional dependencies means that if a →b and a→c then b→c.______ 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.Books(ISBN, Title, CopyrightYear, Author, Publisher, PublisherURL, AuthorEmail )[17 pts] Specify an appropriate primary key. _________________________________[2]Give another candidate key: ________________________________________[2]List all non-trivial functional dependencies. Be sure to identify all determinants. [9]As given, describe two problems with this books relation. [4]For 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:#2 :#3 :#4 :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.b) List all names and zip codes of people who earn more than $50000 in salary.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.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.e) List employee names from the Accounting department who have not traveled (Hint: do a join after set difference). f) List employee names who have traveled to Paris at least twice. (Hint: self-join) ................
................

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

Google Online Preview   Download