Database Management System II - Midterm Examination

Your people lead asked you what table will serve as the parent table in the relationship given that customer.customer_ID (reference key) = order.customer_ID (foreign key).

As a database designer, your Project Manager asked you to create an SQL which will join three tables that will show only the record of a specific person. What type of join is appropriate?

Your project manager asked you to create a one-to-one relationship between two tables, what constraint/key will you put to the attribute in the foreign key/child table?

Your manager wants you to make the attribute name in the customer table to have "No name" as value if a user didn't put any name on it. What type of constraint will you use?

Your lead wants to make the structure of deleting data in your database tables as, the data be deleted first in the child table before the data in parent table. What foreign key option is appropriate?

Your manager wants to make the structure of deleting/updating data in your database tables as, if the data in the parent table is updated/deleted, same goes to the child table. What foreign key option is appropriate?

Between the relation customer with an attribute of customer_ID (primary key) and relation account with an attribute of customer_ID (unique key) where should be the reference key be located?

Your people lead asked you to put a constraint to an attribute of your table which will set to Null value if the attribute has no value in it. What constraint is more likely appropriate?

Your project manager asked you to connect or create relationship to the tables of your database. What appropriate constraint will you use?

Supply the missing parts in the JOIN clause to join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables. SELECT * FROM Orders LEFT JOIN Customers ___________________ = _____________.

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables. SELECT * FROM Orders ________________ ON Orders.CustomerID=Customers.CustomerID;

Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table. SELECT * FROM Orders ____________________ ON Orders.CustomerID=Customers.CustomerID;

An INNER JOIN returns rows of tables where the data _____.

Your manager wants you to return all the records from both tables customers and orders which will return in a very large result-sets of record. What appropriate join is needed in MySQL?

You project lead wants you to put a constraint in an attribute email in customer table that will ensure the values in that attribute always different. What constraint is appropriate?