Partial dependencies are dependencies on only a portion of the ____.

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.