Description
guidance on answers and draft answers Databases SQL diagrams multiple choice
ˆ
ˆ
ˆ
ˆ
ˆ
ˆ
1. Which statement is not correct?
(a) In a database approach, applications don’t have their own les, but all applications access
the same version of the data by interfacing with the DBMS.
(b) In a database approach, the data de nitions or metadata are stored in the applications
accessing the data.
(c) In a database approach, there is typically less storage needed compared to the le approach.
(d) In a database approach, maintenance of data and metadata is easier.
2. In the ER model, a relationship may have attributes associated with it.
(a) True
(b) False
3. In the ER model, only two entities may be connected by a relationship.
(a) True
(b) False
DASC:501
4. For an entity type, an attribute that is underlined indicates that the attribute value is unique
for all entity instances.
(a) True
(b) False
5. Consider the following ER diagram and then select the correct statement:
Middle Name
First Name
Last Name
Age
Name
Person
(a)
(b)
(c)
(d)
A person may have multiple middle names.
A person may have only one middle name.
A person may have multiple names.
The middle names must be unique for all entities.
6. Consider the following ER diagram and select the incorrect statement:
Person
(a)
(b)
(c)
(d)
1..1
owns
0..N
House
A person may own more than one house.
A house must be owned by a person.
A house may be owned by multiple people.
A person may own no houses.
7. Consider the following ER diagram and select the correct statement:
Car
(a)
(b)
(c)
(d)
0..M
rented
0..N
Customer
The rented relationship is a 0:M relationship type.
The rented relationship is a 1:N relationship type.
The rented relationship is a N:M relationship type.
The rented relationship is a N:0 relationship type.
2
DASC:501
8. In the ER modal, entity types are represented by
(a)
(b)
(c)
(d)
Circles
Rectangles
Diamonds
Double circles
9. In the relational model, a relation is de ned as:
(a)
(b)
(c)
(d)
tuple
a set
a set of tuples
a set of sets
10. The tuple (5, 6) = (6, 5)
(a) True
(b) False
11. In the relational model, a super key is a key.
(a) True
(b) False
12. In the relational model, a key is a super key.
(a) True
(b) False
13. In the relational model, two rows may have the same values for all columns in a super key.
(a) True
(b) False
14. In the relational model, foreign key must be unique for all rows.
(a) True
(b) False
15. It is always faster for a RDBMS to execute a query for a database in 3rd normal form than
one that is in 1st normal form.
(a) True
(b) False
16. Select the incorrect statement
(a)
(b)
(c)
(d)
A database in 3rd normal form is in 1st normal form
A database in 2nd normal form may have composite attributes
A database in 4th normal form is in 3rd normal form
A database does not have to always be normalized to 4th normal form
3
DASC:501
17. When mapping a 1 to N relationship between two entity types from the ER model to the
relational model, it is best converted by
(a) Placing a foreign key on the entity type whose entities participate 1 time in the relationship.
(b) Placing a foreign key on the entity type whose entities participate a maximum N times
in the relationship.
(c) Creating a separate table with foreign keys to both entities.
(d) Placing a foreign key on both entities participating in the relationship.
18. When mapping multi-valued attribute types for an entity type from the ER model to the
relational model you must create a separate table.
(a) True
(b) False
19. Consider the following SQL statements. Assume they are executed in the order they appear.
Select the correct statement.
CREATE DOMAIN name_part AS VARCHAR(64);
CREATE TABLE person (first name_part, last name_part);
(a)
(b)
(c)
(d)
The rst and last columns are character strings with a length of at most 64 characters.
You may not insert two rows in the person table with the same value for rst and last.
The rst column is a primary key.
The rst and last columns are character strings with a length of 64 characters.
20. Assume table A references table B via a foreign key relationship. Select the most correct
statement.
(a) DROP TABLE A; must be executed before DROP TABLE B;
(b) DROP TABLE B; must be executed before DROP TABLE A;
(c) Table A and B may not be dropped.
21. Assume the following statements were used to create the table person used to store rst and
last names.
CREATE DOMAIN name_part AS VARCHAR(64);
CREATE TABLE person (first name_part, last name_part);
Assume multiple insertions into the table person have occurred.
Select the most correct statement.
(a)
(b)
(c)
(d)
SELECT * FROM person would return no rows
SELECT first_name, last_name FROM person; would return all rst and last names.
SELECT * FROM person would return all rst and last names.
SELECT first FROM person; would return all last names.
4
DASC:501
22. Select the correct statement. Assume a table has the columns A, B, & C
(a) SELECT count(*) FROM table; always returns the same number as SELECT count(A)
FROM table;
(b) SELECT count(A) FROM table; always returns a number less than or equal to SELECT
count(*) FROM table;
(c) SELECT count(A) FROM table; always returns a number less than SELECT count(*)
FROM table;
(d) SELECT count(*) FROM table; always returns a number less than SELECT count(A)
FROM table;
23. A SQL index will speed up insertions into a table
(a) True
(b) False
24. Assume an index has been created that involves columns A, B, & C. The RDBMS will use
the index to speed up a query involving only the column C.
(a) True
(b) False
25. Assume an index has been created that involves columns A, B, & C. The RDBMS will use
the index to speed up a query involving only the column A.
(a) True
(b) False
5
DASC:501
26. Create an ER diagram that models a restaurant menu and recipe system. Use the following
assumptions:
ˆ A menu has a date/time when it is placed in service.
ˆ A menu has a name, such as “Thanksgiving Specials” or “Breakfast”
ˆ A recipe has a name.
ˆ An ingredient has a name.
ˆ A recipe has multiple ingredients associated with it.
ˆ An ingredient may be used in multiple recipes.
ˆ A menu has multiple recipes.
ˆ A recipe may be on multiple menus.
ˆ When a recipe is associated with a menu, it has a price and category.
6
DASC:501
27. Given the ER diagram below, create the equivalent relational model. Clearly indicate keys
and values that are not null.
SerialNumb
GroupName
Manufacturer
Controller
1..1
has
0..N
SignalGroup
0..1
is_in
0..M
0..N
repairs
RepairDate
SerialNumb
0..N
Technician
FirstName
Middle
Tra cSignal
EmpID
LastName
7
DASC:501
28. Given the relational model below, create SQL CREATE commands that would build an
equivalent database.
Assumptions:
ˆ Any character string columns are no longer than 64 characters.
ˆ userId is an integer.
USER(userId, name)
USER_GROUP(name)
USER_GROUP_MEMBERSHIP(userId , name )
PHONE(number, userId )
CALL(number , toNumber, startDatetime, endDatetime)
EMAIL_ACCOUNT(address, userId )
EMAIL_MESSAGE(msgid, address , messageSubject)
8
DASC:501
29. Normalize the STUDENT relation, ensuring it is in 3rd normal form. Your answer is to be
in the relational model.
This database models students in a school district. Please consider the the following assumptions:
ˆ Items in indicate that the column is multi-valued.
ˆ There are multiple buildings in the school district.
ˆ Each building has multiple classrooms.
ˆ A teacher can teach multiple students.
ˆ A student has one teacher.
ˆ A student takes multiple subjects and is assigned a grade in each.
ˆ A teacher is always in one classroom.
STUDENT(SSN, name( rst, middle, last), assigned_building, assigned_classroom,
assigned_teacher, )
9
DASC:501
30. For sub-questions (a) through (d), consider the database created by running the commands
in the box labeled Create Script. The box labeled Insertion Script contains insertions for
sample data that may aid you in answering your questions.
CREATE TABLE category (cat_id INT PRIMARY KEY, name VARCHAR(64))
CREATE TABLE tvshow (show_id INT PRIMARY KEY, title VARCHAR(64), category INT,
CONSTRAINT FOREIGN KEY fk_cat(category) REFERENCES category(cat_id))
CREATE TABLE schedule(chan_id INT PRIMARY KEY, channel_name VARCHAR(64))
CREATE TABLE airdate(chan_id INT, show_id INT, starts DATETIME,
CONSTRAINT FOREIGN KEY fk_chan(chan_id) REFERENCES schedule(chan_id),
CONSTRAINT FOREIGN KEY fk_show_id(show_id) REFERENCES tvshow(show_id),
PRIMARY KEY(chan_id, show_id))
Create Script
INSERT INTO category VALUES(1, “SciFi”)
INSERT INTO category VALUES(2, “Comedy”)
INSERT INTO category VALUES(3, “News”)
INSERT INTO tvshow VALUES(1, “Frasier”, 2)
INSERT INTO tvshow VALUES(2, “Star Trek: DS9”, 1)
INSERT INTO schedule VALUES(1, “NBC”)
INSERT INTO schedule VALUES(2, “UPN”)
INSERT INTO airdate VALUES(1, 1, “2020-12-12 20:00:00”)
INSERT INTO airdate VALUES(2, 2, “2020-12-13 20:00:00”)
Insertion Script
(a) Create an SQL query that outputs the channel name, title, and start date for every show
scheduled to appear.
(b) Create an SQL query that outputs the channel name, title, and start date for every show
scheduled to appear on 12 December, 2020.
(c) Create an SQL command that will index the name of a TV show.
(d) Create an SQL command that will update the airdate table to include the name of the
episode.
10
Purchase answer to see full
attachment
