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
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.
