QuestionQuestion

Notes: Write legibly. Answer all questions. Think them through.

1. Mark T (true) or F (false)
a. In ER modeling, a weak entity always has a composite primary key.
b. A view does not actually store any data.
c. In SQL, all queries automatically eliminate redundant tuples from their outputs.
d. An alternate key is also a candidate key.
e. ALTER TABLE is a DML command.

2. Briefly discuss. Just write the key points.
a. What are the different purposes of using the ER model and the relational model, respectively?
b. What are the roles of foreign keys in relational databases?
c. Suppose we have a table for keeping track of the history of employees' salary as follows: SAL_HIST (Emp#, Salary, Reason, Raise_Date) representing how many times the employee had raises up to now. What field or combination of fields could be the primary key? Explain your reasoning. (4)
d. What are the usages of views in the relational database? (4)
e. What is the ACID properties in relational databases? Name and describe each one. Why are they important? (4)
f. What are the advantages and disadvantages of having many indexes for a table?

3. The following questions ask you to check the syntax of SQL commands. For the questions of 3(a) and 3(b) below, use the following relational schema on properties and clients who viewed the properties for purchase. For the ease of identification, all PKs are both underlined and bold-faced.
Branch (B#, street, city, zipcode)
Client (C#, cName, street, city, tellNum, preferType)
Property (P#, street, city, zipcode, type, rent, #rooms, B#)
FK B# references Branch (B#)
Viewing (C#*, P#*, viewDate, comment)
FK C# references Client (C#)
FK P# references Property (P#)

3(a). Is the following command correct? If not, correct the syntax. You can just rewrite/correct only the wrong parts (3)
CREATE TABLE viewing (
C#            CHAR(6) PRIMARY KEY,
P#            CHAR(6) PRIMARY KEY,
viewDate DATE,
Comment          VARCHAR2(100),
FOREIGN KEY (C#) REFERENCES Client(C#),
FOREIGN KEY (P#) REFERENCES Property(P#));

3(b). Is the following command correct? If not, correct the syntax. You can just rewrite/correct only the wrong parts. (3)
SELECT      C#, cName, street, city
FROM         Client C, Property P, Viewing V
WHERE      V.C# = C.C#
AND            V.P# = P.P#
AND            viewDate = ’25-AUG-2012’;

3(c). Is the following command syntactically correct? If not, correct the syntax. You can just rewrite/correct only the wrong parts. (3)
SELECT   x, y, z, count(*)
FROM       t1, t2
WHERE    t1.x = t2.y
GROUP BY       x, y
HAVING       count(*) > 3;
4. Suppose we have a table called Person (P#, name). What is the output from the following sequence of DML commands with ROLLBACK and COMMIT. (4)
INSERT INTO Person VALUES (1, ‘Mary’);
INSERT INTO Person VALUES (2, ‘Jack’);
COMMIT;
INSERT INTO Person VALUES (3, ‘Kate’);
DELETE FROM Person WHERE P# =2;
INSERT INTO Person VALUES (4, ‘Mike’);
ROLLBACK;
SELECT * from Person;

5. Suppose we have a bank schema as shown below. Note that DEPOSIT.BNO is a foreign key to Branch table.
Deposit
ACC#         CNAME    BNO*       BALANCE
101                LISA          1               100
102                LISA          2               500
103                TOM          1               400
104               JOHN         3               1200
105                TOM          3               900

Branch
BNO       MANAGER_NAME      Salary       MGRSTARTDATE
1                      BOB                   100000       19-Jun-2001
2                      CHRIS                  150000       01-Jan-2005
3                     ANGELA                90000          22-May-1998
4                         KIM                   90000          29-May-1996

(a) Show the SQL query “Find customer name and balance of all the customers of the branch managed by BOB.” You can use any SQL syntax. (5)
(b) Show the output of the above query. (5)
(c) How many comparisons do you need to answer the query shown in (a) above? (3)
(d) For each branch, find the manager names and the #customers they are managing and the total deposit balance of the customers they manage. (5)
(d) Show the output of the query. (5)

6. Based on the relational schema below, write SQL commands for the following problem. Note that “*” at the end of attribute name indicates that the attribute is a FK. PRODUCT.PRICE is a selling price; SUPPLY.S_PRICE is a supplying price.
(Show SQL statements only; no need to show output).
PRODUCT (P#, P_NAME, PRICE, QTY)
SUPPLY(S#*, P#*, S_PRICE)
SUPPLIER(S#, Sname, City).

(a) Find all supplier names and cities who supply expensive chairs, where expensive chairs are those whose selling price is greater than $300. (5)
(b) Find all the product numbers whose selling price is greater than that of DRESSER ? (5)
(c) Find minimum, maximum, and average selling price of products from each supplier. Show also Supplier number and supplier name.(5)

7. Show the ERD with relational notation with a “crow’s foot” style.
Drexel University’s Financial Office has made contracts with several local banks to help its university members to easily obtain small loans to $10,000 for each year. For this purpose, there are two types of university members - either students or employees. A student loan has a low interest rate, and students will begin to pay back after one year of their college graduation with a fixed amount per month. An employee loan has a higher interest rate than student loans, and employees need to pay certain amounts each month after the 2nd month of the loan approval. Each loan belongs to only one bank. Obviously, a bank approves many loans. For each loan, there is a loan#, interest rate, approved amount, monthly payment amount, and the beginning date of loan payments. For each loan, the office also keeps tracks of the history of payments to the loan, including payment date and payment amount. The office also wants to know university member ID, name, and address. In addition, each student has a major, while faculty and staff have the department name they work for and their job titles. For banks, we just keep bank number and name.
You may add other unstated, but common-sense oriented, facts to the ERD, but you must represent the facts stated above. (15)

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

1.a. F
b.T
c. F
d.T
e. F
2. Briefly discuss. Just write the key points.
a. What are the different purposes of using the ER model and the relational model, respectively? (4)
Answer:
ER model: It reflects the collection and relationship between objects called entities. Entity Relationship Model describes information as a set of entities, a set of relationships and an attribute. E-R Model makes the connection between entities easier to comprehend. The E-R model defines the cardinalities of mapping.
Relational Model: It reflects the tables collection and the relationship between the tables. Relational Model defines information as domain, attributes, and tuples in a table. Comparatively, a relationship between tables in the Relational Model is less simple to obtain. The Relational Model does not portray cardinal mapping.

b. What are the roles of foreign keys in relational databases? (4)
A foreign key in a relational database table is a column or group of rows that offers a connection between information in two tables. It functions as a cross-reference between tables as it refers to another table's primary key, thus creating a connection between them. Most tables in a scheme of relational databases conform to the notion of foreign key. Data in a domain must be introduced across various tables in complicated databases and data warehouses, thereby preserving a connection between them. The referential integrity idea is obtained from the theory of foreign key.

c. Suppose we have a table for keeping track of the history of employees' salary as follows: SAL_HIST (Emp#, Salary, Reason, Raise_Date) representing how many times the employee had raises up to now. What field or combination of fields could be the primary key? Explain your reasoning. (4)
Emp# could be primary key as this can identify...

By purchasing this solution you'll be able to access the following files:
Solution.docx.

$40.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats