Description
This assignment gets you started thinking like a database designer. You will learn concepts from chapters 3 through 8 and and implement them using MS Access or other DBMS.
The activities of this assignment are covered in Modules 02 through 08 and implement course objectives #1 through 4.
Each of the questions from this quiz are related to a database that you are to implement in MS Access or other DBMS. To prepare for the quiz you should do the following activities:
Step 1: Create the following tables using MS Access or other DBMS.
This database will require the following entities (shown below). The primary key fields are denoted in bold letters, and the foreign keys are denoted in italics and underlined.
Account(ANO, ANAME, STREET, CITY, STATE, ZIP)
Transactions(TID, TDATE, AMOUNT, STOREID, ANO)
Store(STOREID, SNAME, STREET, CITY, STATE, ZIP)
One account can have many transactions and one store can have many transactions.
When you take the quiz you will confirm that you created the tables.
Step 2: Create an ERD for this situation.
When you take the quiz you will be required to identify the correct ERD from multiple choices.
Step 3: Confirm the tables are presented in the assignment as a Relational Model.
When you take the quiz you will confirm that the tables are defined in a correct Relational Model format.
Step 4: Insert rows in the tables, at least 5 stores, 5 accounts, and 30 transactions.
When you take the quiz you will confirm that you populated the tables.
Step 5: Create a form that allows a user to insert and update data for a table.
When you take the quiz you will attest that you created the form.
Step 6: Create a report that lists all accounts.
When you take the quiz you will be required to identify the correct SQL for this report from multiple
choices.
Step 7: Create a report that lists all transactions for a given day.
When you take the quiz you will be required to identify the correct SQL for this report from multiple choices.
Step 8: Create a report that is a monthly statement for a given individual account that lists the date, Sname, and the amount of the transaction for that account. The user must specify the
month. At the end of the statement, the total amount should be summed.
When you take the quiz you will be required to identify the correct SQL for this report from multiple choices.
Step 9: Create a report that lists all transactions for a given account number.
When you take the quiz you will be required to identify the correct SQL for this report from multiple choices.
Step 10: Create a query that inserts a row into the Transaction table.
When you take the quiz you will be required to identify the correct SQL for this step from multiple choices.
Step 11: Study Chapter 6: Normalization
When you take the quiz you will be required to identify remedies to normalization violations from multiple
choices.
Take the Quiz Again
Which one of the following UML models is correct for this situation:
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
https://canvas.vt.edu/courses/102788/quizzes/171060 4/9
a
b
c
d
Question 3 2 / 2 pts
The entities and relationships for the situation are presented in the
assignment in the form of a relational model.
True
False
Question 4 2 / 2 pts
Which of the following SQL statements is correct for a report that list all
accounts?
SELECT * FROM transactions;
SELECT * FROM store;
SELECT ano, aname FROM account INNER JOIN transactions ON ano;
SELECT ano, aname FROM account ORDER BY ano;
Question 5 2 / 2 pts
Which of the following SQL statements is correct for a monthly statement
for a given individual account that lists the date, Sname, and the amount
of the transactions for that account. The user must specify the account
number, month, and year.
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
https://canvas.vt.edu/courses/102788/quizzes/171060 6/9
SELECT tdate, sname, amount FROM transactions WHERE ano =
[ANO_REQUESTED] AND (MONTH(tdate ) = [MONTH_REQ] AND
YEAR(= [YEAR_REQ] );
SELECT tdate, sname, amount FROM transactions INNER JOIN store ON
transactions.storeid = store.storeid WHERE ano = [ANO_REQUESTED]
AND (MONTH(tdate ) = [MONTH_REQ] AND YEAR(tdate) = [YEAR_REQ]
) ORDER BY tdate, sname;
SELECT tdate, aname, amount FROM transactions WHERE ano =
[ANO_REQUESTED] AND (MONTH(tdate ) = [MONTH_REQ] AND
YEAR(tdate) = [YEAR_REQ] ) ORDER BY tdate, sname;
SELECT tdate, sname, amount FROM transactions WHERE ano =
[ANO_REQUESTED] AND (tdate = [MONTH_REQ] + [YEAR_REQ] )
ORDER BY sname;
Question 6 2 / 2 pts
Which of the following SQL statements is correct for the total line of
monthly statement for a given individual account for a month and year.
The user must specify the account number, month, and year.
SELECT tdate, SUM(amount) FROM transactions WHERE ano =
[ANO_REQUESTED] AND (MONTH(tdate ) = [MONTH_REQUESTED]
AND YEAR(tdate) = [YEAR_REQUESTED] ) GROUP BY tdate;
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
https://canvas.vt.edu/courses/102788/quizzes/171060 7/9
SELECT MONTH(tdate), YEAR(tdate), amount FROM transactions
WHERE ano = [ANO_REQUESTED] AND (MONTH(tdate ) =
[MONTH_REQUESTED] AND YEAR(tdate) = [YEAR_REQUESTED] )
GROUP BY MONTH(tdate), YEAR(tdate);
SELECT MONTH(tdate), YEAR(tdate), SUM(amount) FROM transactions
WHERE ano = [ANO_REQUESTED] AND (MONTH(tdate ) =
[MONTH_REQUESTED] AND YEAR(tdate) = [YEAR_REQUESTED] )
GROUP BY MONTH(tdate), YEAR(tdate);
SELECT MONTH(tdate), YEAR(tdate), SUM(amount) FROM transactions,
account WHERE ano = [ANO_REQUESTED] AND (MONTH(tdate ) =
[MONTH_REQUESTED] AND YEAR(tdate) = [YEAR_REQUESTED] )
GROUP BY MONTH(tdate), YEAR(tdate);
Question 7 2 / 2 pts
Which of the following SQL statements is correct for a list of all the
transactions for a requested account number.
ELECT tdate, sname, amount FROM account WHERE ano =
[ANO_REQUESTED];
SELECT tdate, sname, amount FROM transactions, store WHERE
transactions.storeid = store.storeid AND ano = [ANO_REQUESTED];
SELECT tdate, sname, amount FROM account, transactions WHERE
account.ano = [ANO_REQUESTED];
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
https://canvas.vt.edu/courses/102788/quizzes/171060 8/9
SELECT tdate, sname, amount FROM transactions, account WHERE
transaction.ano = [ANO_REQUESTED];
Question 8 2 / 2 pts
Denormalization produces a lower normal form.
True
False
Question 9 2 / 2 pts
Dependencies that are based on only a part of a composite primary key
are called transitive dependencies.
True
False
Question 10 2 / 2 pts
Since a partial dependency can exist only if a table’s primary key is
composed of several attributes, if a table in 1NF has a single-attribute
primary key, then the table is automatically in 2NF.
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
https://canvas.vt.edu/courses/102788/quizzes/171060 9/9
True
False
Question 11 2 / 2 pts
Attribute A _____ attribute B if all of the rows in the table that agree in
value for attribute A also agree in value for attribute B.
determines
derives from
controls
owns
none of the above
Quiz Score: 25 out of 25
