ACIS5504-Assignment 1 Introduction to ER Modeling and using a DBMS Solved

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

Question 1
I confirm, consistent with VT Honor Code, that I completed all of the
following activities for this assignment: created a database including the
three tables indicated; inserted at least 5 stores, 5 accounts, and 30
transactions into the tables in my database; created a form that allows a
table rows to be inserted and updates; and created the SQL and
associated reports requested.
Yes, I completed all of the hands-on tasks
No, I was unable to complete all of the hands-on tasks
Ques 2 / 2 pts tion 2
Which one of the following UML models is correct for this situation:

a
b
c
d
Question 3

The entities and relationships for the situation are presented in the
assignment in the form of a relational model.
True
False Question 4
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
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.

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

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
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];

SELECT tdate, sname, amount FROM transactions, account WHERE
transaction.ano = [ANO_REQUESTED];
Question 8
Denormalization produces a lower normal form.
True
False
Question 9
Dependencies that are based on only a part of a composite primary key
are called transitive dependencies.
True
False
Question 10
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.

True
False
Question 11
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