Part V: SQL (10 questions, each 5 points): Given the ERD, please answer the questions using SQL 34. For every boat, list the marina number, slip number, boat name, owner number, owner’s first and last name. 35. For every completed or open service request for routine engine maintenance, list the slip ID, Description, and status. (description of the serviceRequest is ‘routine engine maintenance’) 36. For every service request for routine engine maintenance, list the slip ID, marina number, slip number, estimated hours, spent hours, owner number, and owner’s last name. 37. List the first and last names for all owners who have a boat in a 40-foot slip. (Use the IN operator in your formulation) 38. List the first and last names for all owners who have a boat in a 40-foot slip. (Use the EXISTS operator in your formulation) 39. List the names of any pair of boats that have the same type. For example, one pair would be Anderson II and Escape, because the boat type for both boats is Sprite 4000. The first name listed should be the major sort key and the second name should be the minor sort key. (This is a SELF JOIN) 40. List the boat name, owner number, owners last and first name for each boat in marina 1. 41. List the boat name, owner number, owners last and first name for each boat in marina 1, but only list boats in 30-foot slips. 42. List the marina number, slip number, and boat name for boats whose owner lives in Glander Bay OR whose type is Sprite 4000. 43. List the marina number, slip number, and boat name for boats whose owner lives in GlanderBay AND whose type is Sprite 4000 ECE 59500 Database Management Systems Quiz 2 (Spring 2016) A 1. Storing the same data in more than one place is called ____. data independence redundancy data integrity security 2. A(n) ____ is also called a field or column in many database systems. attribute entity data file relationship 3. A(n) ____ is a person, place, object, event, or idea for which you want to store and process data. attribute DBMS entity DBA 4. An association between entities is known as a(n) ____. relationship integrity constraint database data file 5. A visual way to represent a database is with a(n) ____. spreadsheet DBMS entity-relationship diagram DBA 6. During the ____ process, a database expert determines the structure of the required database. data security database integrity database design database selection 7. An integrity constraint is a rule that ____. is kept in an external file data must follow in the database can be accessed only by authorized users can unintentionally be accessed by unauthorized users 8. ____ is the prevention of unauthorized access to the database. Data independence Integrity constraint Redundancy Security 9. When duplicate column names exist in a database and you need to indicate the column to which you are referring, ____. do not use these two tables together do not use the column names in the same statement write both the table name and the column name, separated by a period write the table name only 10. The ____ key of a table is the column or collection of columns that uniquely identifies a given row in that table. primary secondary foreign minor 11. A table that contains a repeating group is called a(n) ____. normalized relation unnormalized relation nominal relation non-nominal relation 12. Second normal form can be defined as a table that is in first normal form but that contains no ____. alternate keys nonkey columns interrelation constraints partial dependencies 13. ____ integrity is the constraint that says that the values in foreign keys must match values already in the database. Parallel Relational Conditional Referential 14. An alternate key is a _____. foreign key primary key column that could be a primary key but was not chosen row that could be a primary key but was not chosen 15. The anomalies of update, inconsistent data, addition, and deletion occur when a column is _____. dependent on the primary key dependent on only a portion of the primary key dependent on an alternate key not dependent at all Part II: True/False questions (15 questions, each 2 points) 16. In DBDL, you represent a table by listing all columns and then underlining the primary key. 17. A foreign key is a column or collection of columns in one table that is required to match the value of the primary key for some row in another table, or be null. 18. The primary key
is a unique identifier. 19. The normalization process used to convert a relation or collection of relations to an equivalent collection of third normal form tables is a crucial part of the database design process. 20. If the primary key of a table contains only a single column, the table is automatically in first normal form. 21. In general, when converting a non-first normal form table to first normal form, the primary key will usually include the original primary key concatenated with the key to the repeating group. 22. Removal of repeating groups is the starting point in the quest to create tables that are as free of problems as possible. 23. If B is functionally dependent on A, you can also say that B functionally determines A. 24. A table that is in first normal form is better than one that is in second normal form. 25. An unnormalized relation is a table that has more than one row. 26. A relational database is a collection of relations. 27. In a relation, the order of rows is important. 28. In a relation, the order of the columns is important. 29. An entity is also defined as an attribute. 30. A database will not only hold information about multiple types of entities, but also information about the relationships among these multiple entities. Part III: Short Answers (2 questions, each 10 points) 31. Concerning the Logical Database Design Process, discuss why we should not list or store multiple pieces of data (repeating groups) in the same field in our tables. 32. Provide a 3 rd Normal Form logical design of a relational database for the following data: Household Street address ZipPeopleEmployerEmployerCity Lawrence 1319 Ozkan St 02169 Jim Lawrence Judy Lawrence John Lawrence Yates 121 Broad Dr 46032 Mark Yates Sofia Kim Jeff Yates Andy Yates Part V: Database Design and ERD (1 question, 20 points) Harvard Univ NA NA Dish IUPUI NA IIT Boston, IN NA NA Chicago, IL Indianapolis, IN NA Carmel, IN 33. The School of Informatics wants to design a new relational database system to keep track of research activities among faculty and students, which the following requirements: (1) Each Project has one and only one description associated with it. (2) A Faculty member can have one or more Projects and a Project can be worked by one or more Faculty members. (3) A Faculty member can be the chair of one or more Students’ work but one Student only can have one chair Faculty (4) A Student can work on one or more Projects and a Project can be worked by one or more students. For each question you will draw an E-R Diagram. The diagram should identify the Entities in each question and the relationship between each Entity. Remember to treat each question as a separate E-R Diagram. Then combine the individual E-R Diagrams into one comprehensive diagram. ECE59500 Assignment 1 (1) Using your system database account, create a user as ecehw2; give the user the needed (but not more) privileges to perform the following tasks Submit the SQL statements for the tasks. Using the above created user to work on part (2), (3) and (4). (2) Based on the solutions provided for homework 1, create the tables with the additional information below. Make sure to have the PK and FK constraints in your SQL statements. Submit the SQL statements for the tasks. Book (bookId, ISBN, title, authors, publisher) All varchar2 type Newbook (bookId, location, quantity, price) All varchar2 type except quantity and price should be numbers Usedbook (bookId, location, quantity, price) All varchar2 type except quantity and price should be numbers Customer (customerId, firstName, lastName, credit) All varchar2 type except credit should be numbers Supplier (supplierId, name, address) All varchar2 type BuyFrom (transactionId, customerId, bookId, price, quantity, buyTime) All varchar2 type except quantity and price should be numbers buyTime should be Date type sellTo (transactionId, customerId, bookId, condition, price, quantity, sellTime) All varchar2 type except quantity and price should be numbers sellTime should be Date type Orders (orderId, supplierId, bookId, price, quantity, orderTime) All varchar2 type except quantity and price should be numbers orderTime should be Date type (3) Insert the records (to be provided in a separate excel file). Submit the SQL statements for the tasks. (4) Populate the records (to be provided in a separate file). Submit the SQL statements for the tasks.