[Solved] ACIS5504 Assignment 1-1-Introduction to ER modeling and using a DBMS

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 multiplechoices.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 themonth. 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: NormalizationWhen you take the quiz you will be required to identify remedies to normalization violations from multiplechoices.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 2020https://canvas.vt.edu/courses/102788/quizzes/171060 4/9abcdQuestion 3 2 / 2 pts

The entities and relationships for the situation are presented in theassignment in the form of a relational model.TrueFalseQuestion 4 2 / 2 ptsWhich of the following SQL statements is correct for a report that list allaccounts?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 ptsWhich of the following SQL statements is correct for a monthly statementfor a given individual account that lists the date, Sname, and the amountof the transactions for that account. The user must specify the accountnumber, month, and year.3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020https://canvas.vt.edu/courses/102788/quizzes/171060 6/9SELECT tdate, sname, amount FROM transactions WHERE ano =[ANO_REQUESTED] AND (MONTH(tdate ) = [MONTH_REQ] ANDYEAR(= [YEAR_REQ] );SELECT tdate, sname, amount FROM transactions INNER JOIN store ONtransactions.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] ANDYEAR(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 ptsWhich of the following SQL statements is correct for the total line ofmonthly 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 2020https://canvas.vt.edu/courses/102788/quizzes/171060 7/9SELECT MONTH(tdate), YEAR(tdate), amount FROM transactionsWHERE 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 transactionsWHERE 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 ptsWhich of the following SQL statements is correct for a list of all thetransactions for a requested account number.ELECT tdate, sname, amount FROM account WHERE ano =[ANO_REQUESTED];SELECT tdate, sname, amount FROM transactions, store WHEREtransactions.storeid = store.storeid AND ano = [ANO_REQUESTED];SELECT tdate, sname, amount FROM account, transactions WHEREaccount.ano = [ANO_REQUESTED];3/28/2020 Assignment 1: Introduction to ER modeling and using a DBMS: Sys/Database Concepts Spring 2020https://canvas.vt.edu/courses/102788/quizzes/171060 8/9SELECT tdate, sname, amount FROM transactions, account WHEREtransaction.ano = [ANO_REQUESTED];Question 8 2 / 2 ptsDenormalization produces a lower normal form.TrueFalseQuestion 9 2 / 2 ptsDependencies that are based on only a part of a composite primary keyare called transitive dependencies.TrueFalseQuestion 10 2 / 2 ptsSince a partial dependency can exist only if a table’s primary key iscomposed of several attributes, if a table in 1NF has a single-attributeprimary 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 2020https://canvas.vt.edu/courses/102788/quizzes/171060 9/9TrueFalseQuestion 11 2 / 2 ptsAttribute A _____ attribute B if all of the rows in the table that agree invalue for attribute A also agree in value for attribute B.determinesderives fromcontrolsownsnone of the aboveQuiz Score: 25 out of 25