325 Final
When applying the M-M relationship rule for converting an ERD into a relational table design, which of the following statements is true:
A and C
For the generalization hierarchy rule, which of the following is true?
All of the above
When trying to determine a primary key for and entity, what characteristics of an attribute make an attribute a potential primary key?
All of the above
Which of the following should be included in design documentation?
All of the above
Which of the following is not an output of the Traditional Systems Development Life Cycle?
All the above
Indicate the SQL statement that will add a "Phone_Number" column to a table called Students. Use the appropriate data type
Alter table Students add Phone_Number varchar(13)
Which of the following statements is not true of a desktop DBMS?
Although useful for processing ad hoc queries, they cannot perform transaction processing.
A table that satisfies 2NF
Always satisfies 1NF
A table that satisfies BCNF
Always satisfies 3NF
A collection of components that support data acquisition, dissemination, maintenance, retrieval, and formatting.
DBMS
A support position that specializes in managing individuals databases and DBMSs.
Database administrator
Indicate the SQL query that will delete all records from the STUDENTS table where the value in the "Last_Name" field begins with the letter "A"
Delete students where last_name like 'A%'
Indicate the query that will delete a table called STUDENTS
Drop Table Students
Which of the following is a level of database description in the Three Schema Architecture?
External
A 1-M relationship is a connection between two tables in which rows of each table can be related to many rows of the other table.
False
A self-referencing relationship is always a 1-M relationship.
False
Design decisions rarely need to be documented, as the final data model should be documentation enough.
False
In a client-server environment, DBMS software typically executes only on the server.
False
When transforming an ERD into a relation table design, the primary key of the child side of a 1-M relationship (the many side) will become a foreign key in the 1 side of the relationship.
False
Which of the following is not a stage in the Traditional Systems Development Life Cycle?
Feedback
Converting from an ERD to a table design.
Forward Engineering
The following SQL query is an example of a ____ join. SELECT FacNo, FacFirstName, FacLastName, FacSalary, StdNo FROM Faculty RIGHT JOIN Student ON Student.StdNo = Faculty.FacNo UNION SELECT FacNo, FacFirstName, FacLastName, FacSalary, StdNo From Faculty LEFT JOIN Student ON Student.StdNo = Faculty. FacNo
Full Outer
Use the following table definitions to answer the question below. customer(custno, name, address, city, state, zip, phone, income) asset(assetno, assetdesc, assettype) service(servno, servdesc, servrate) employee(empno, empname, emptitle) pricehistory(assetno, date, price) FOREIGN KEY assetno REFERENCES asset charge(chargeno, custno, servno, starttime, endtime, empno, billed, amtpaid) FOREIGN KEY custno REFERENCES customer FOREIGN KEY servno REFERENCES service FOREIGN KEY empno REFERENCES employee holding(custno, assetno, qty) FOREIGN KEY custno REFERENCES customer FOREIGN KEY assetno REFERENCES asset trade(tradeno, custno, assetno, qty, price, date, type, status) FOREIGN KEY custno REFERENCES customer FOREIGN KEY assetno REFERENCES asset What table contains a combined primary key that represents a M-N relationship?
Holding
In an ERD, solid lines connecting entities represent
Identifying relationships
The following SQL query is an example of a ____ join. SELECT FacNo, FacFirstName, FacLastName, FacSalary, StdNo FROM Faculty, Student WHERE Student.StdNo = Faculty.FacNo
Inner
Which of the following statements is not true of information resource management?
It is very different and much more challenging than managing the other physical resources of an organization.
Following a power failure, the database is restored with a wrong price for a data item. This break in data quality was caused by:
Lack of Reliability
The following SQL query is an example of a ____ join. SELECT FacNo, FacFirstName, FacLastName, FacSalary, StdNo FROM Faculty LEFT JOIN Student ON Student.StdNo=Faculty.FacNo
Left-Outer
A goal of normalization is to:
Minimize data redundancy
A language, such as SQL, that allows you to specify the parts of a database to access rather than to code a complex procedure.
Non-procedural database language
For a violation of 2NF to occur in a table in 1NF, the following condition(s) must exist:
Part of a key determines a nonkey attribute
What query produces the same results as the one below? SELECT Student.StdNo, Student.StdFirstName, Student.StdLastName, Student.StdMajor FROM Student WHERE NOT EXISTS(SELECT * FROM Enrollment WHERE Enrollment.StdNo = Student.StdNo)
SELECT Student.StdNo, Student.StdFirstName, Student.StdLastName, Student.StdMajor FROM Student LEFT OUTER JOIN Enrollment ON Enrollment.StdNo = Student.StdNo WHERE Enrollment.StdNo IS NULL
An industry standard database language that includes statements for database definition, database manipulation, and database control.
SQL
Indicate the SQL statement that will return the number of rentals issued to customer BLACK during 2009. Call this total "NumRentals"
Select Count(*) as NumRentals from Rentals where CName = 'Black' and year(date_out) = 2009
Indicate the SQL code that will return the unique set of car "Makes" rented by customer BLACK. Sort the result in descending order by Make
Select distinct Make from Rentals where CName = 'Black' order by Make desc
What is the meaning of the following query. SELECT StdNo, StdFirstName, StdLastName, StdMajor FROM Student WHERE NOT EXISTS(SELECT * FROM Enrollment WHERE Enrollment.StdNo = Student.StdNo)
Show all of the students who do not have records in the Enrollment table
A named, two-dimensional arrangement of data consisting of a heading part and a body part.
Table
Which one of the following indicates poor data quality?
The same supplier is shown with two different addresses in two parts of the database.
3NF/BCNF is the most important normal form in practice because normal forms higher than this involve other kinds of dependencies that are less common and more difficult to understand.
True
A common mistake in ERD diagramming is reversing the cardinality of an identifying relationship.
True
A one-sided outer join can be useful in preserving rows in the result table in a situation where a table has null values in a foreign key.
True
Data modeling is usually an iterative or repetitive process. You construct a preliminary data model and then refine it many times.
True
In a relationship, the association between entity types is bidirectional.
True
One of the disadvantages of the traditional life cycle is that the rush to implement a visible system often causes insufficient time to be allocated to the analysis and design phases.
True
One type of data model transformation is to add historical details to a data model. Historical details may be necessary for legal requirements as well as strategic reporting requirements.
True
Persistence of data in a database is dependent on the relevance of the business's intended usage
True
The ON DELETE CASCADE clause of a foreign key constraint statement would cause related rows in another table to be deleted automatically.
True
To avoid modification anomalies in a database, it is better to modify the table design than to work around them by writing code or using dummy values.
True
Two tables that are not union compatible can be made union compatible using the project operator
True
The following SQL query is an example of a ____ query. SELECT StdNo, StdFirstName, StdLastName, StdMajor FROM Student WHERE Student.StdNo IN(SELECT StdNo FROM Enrollment WHERE EnrGrade >= 3.5)
Type I nested
The following SQL query is an example of a ____ query. SELECT StdNo, StdFirstName, StdLastName, StdMajor FROM Student WHERE NOT EXISTS(SELECT * FROM Enrollment WHERE Enrollment.StdNo = Student.StdNo)
Type II nested
Indicate the correct SQL code that will change the Age of Customer WILSON from 25 to 45.
Update Customer Set Age = 45 Where CName = 'Wilson'
In the space provided below, write the SQL code that will set your current database to ECHO. Note that ECHO has already been created for you.
Use ECHO
What query produces the same results as the one below? SELECT Student.StdNo, Student.StdFirstName, Student.StdLastName, Student.StdMajor FROM Student WHERE EXISTS(SELECT * FROM Enrollment WHERE Enrollment.StdNo = Student.StdNo)
a. SELECT Student.StdNo, Student.StdFirstName, Student.StdLastName, Student.StdMajor FROM Student, Enrollment WHERE Enrollment.StdNo = Student.StdNo b. SELECT Student.StdNo, Student.StdFirstName, Student.StdLastName, Student.StdMajor FROM Student WHERE StdNo IN(SELECT DISTINCT StdNo FROM Enrollment) c. SELECT Student.StdNo, Student.StdFirstName, Student.StdLastName, Student.StdMajor FROM Student LEFT OUTER JOIN Enrollment ON Enrollment.StdNo = Student.StdNo WHERE Enrollment.StdNo IS NOT NULL D. All of the above
Use the following table definitions to answer the question below. customer(custno, name, address, city, state, zip, phone, income) asset(assetno, assetdesc, assettype) service(servno, servdesc, servrate) employee(empno, empname, emptitle) pricehistory(assetno, date, price) FOREIGN KEY assetno REFERENCES asset charge(chargeno, custno, servno, starttime, endtime, empno, billed, amtpaid) FOREIGN KEY custno REFERENCES customer FOREIGN KEY servno REFERENCES service FOREIGN KEY empno REFERENCES employee holding(custno, assetno, qty) FOREIGN KEY custno REFERENCES customer FOREIGN KEY assetno REFERENCES asset trade(tradeno, custno, assetno, qty, price, date, type, status) FOREIGN KEY custno REFERENCES customer FOREIGN KEY assetno REFERENCES asset If the foreign key constraint for the Trade.CustNo column includes ON DELETE RESTRICT, deleting a row of the Customer table
will not be allowed if there are related rows in the Trade table
