ACIS5504- Assignment 1 Solved

Instructions

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.
https://canvas.vt.edu/courses/102788/quizzes/171060 1/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020

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

Attempt LATEST Attempt 1
Take the Quiz Again

Time

51 minutes
Score

25 out of 25
 Answers will be shown after your last attempt

Score for this attempt: 25 out of 25 Submitted Feb 27 at 12:19am
https://canvas.vt.edu/courses/102788/quizzes/171060
2/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
This attempt took 51 minutes.
Question 1
5 / 5 pts
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
Question 2
2 / 2 pts
Which one of the following UML models is correct for this situation:
https://canvas.vt.edu/courses/102788/quizzes/171060 3/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
a b c d
Question 3
2 / 2 pts
https://canvas.vt.edu/courses/102788/quizzes/171060 4/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
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;
The entities and relationships for the situation are presented in the assignment in the form of a relational model.

True False
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.
https://canvas.vt.edu/courses/102788/quizzes/171060 5/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
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;
https://canvas.vt.edu/courses/102788/quizzes/171060 6/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
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];
https://canvas.vt.edu/courses/102788/quizzes/171060 7/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
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
SELECT tdate, sname, amount FROM transactions, account WHERE transaction.ano = [ANO_REQUESTED];
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.
https://canvas.vt.edu/courses/102788/quizzes/171060 8/9
3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020
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
True False
https://canvas.vt.edu/courses/102788/quizzes/171060 9/9