Problem 1. Please answer briefly. 1. Compare the following terms briefly: Primar

Question

Problem 1. Please answer briefly.

1. Compare the following terms briefly: Primary Key, Candidate Key, Super key. Give examples of these concepts.
2. Every weak entity set can be converted to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets? Give an example to explain (one using the weak entity set and one without using it, and explain the difference).
3. Give an example of how a ternary relationship can be broken down to two binary relationships (you cannot use the examples on the slides or the textbook). Each entity must have at least two attributes. Explain why the binary relationships is a better choice than the ternary relationship in your example.
4. Give two examples on
a. One-to-one relationship
b. One-to-many relationship
c. Many-to-many relationship

Problem 2. Suppose you’re hired by a startup company that plans to build an online music store similar to the iTunes Store. Your first task is to design a database that stores and organizes all the music that the company sells. The database stores information about the artists: their names (assuming unique), birthplaces, age, and style of music. For each piece of music: the artist, the year it was released, the unique title, the title of the album it appears in, and the price (yes, they’re abandoning the uniform-price model) are to be stored. The music are classified into different categories such as soft rock, 80’s music, country, classical, opera, love songs, breakup songs, etc. A given piece of music can belong to more than one category. Each category is given a label (like the ones mentioned above) that identifies the category. The database also stores information about the customers. For each customer, the name, address, gender, age, favorite artists, favorite categories of music, and total amount spent so far, are to be stored. You may assign a unique customer id for each customer.
Given the above description, do the following:
1. Draw an ER-diagram for the database, identifying the following:
(i) all the entity sets;
(ii) all the relationship sets and their multiplicity;
(iii) the primary key for each entity set (and weak entity set, if any) and each relationship set.

Invent more attribute(s) for the entity sets if you like.
State any assumptions you make for your ER-diagram.
2. Indicate (what and why) feature(s)/property(ies) (if any) in the above description that are NOT captured by your ER-diagram;
3. Give 2 examples of the types of reports that can be obtained from the database, and state the involved entity sets and/or relationship sets. Each report example must involve at least two entity/relationship sets; (For example, an report can be “List all the customers that enjoy rock who have spent more than $50 on purchase”)
4. Convert the entity-relationship design to a scheme for a relational database. List all relation schemes. For each relation scheme, state
(i) the name of the relation,
(ii) the names of its attributes,
(iii) the domain (or data type) of each attribute,
(iv) the primary key,
(v) the foreign key(s);
5. Use Oracle on ITE LAB machine to create the tables from 4 above and insert at least two tuples to each table. Implement all these in one script (text) file.

Explanation / Answer

A candidate key is a set of attributes such that there are no two distinct tuples with the same values for these attributes.

For example, In a car database table, license number and engine serial number combinedly become a candidate key.

Super key is any Unique key with some Non Unique key combination.

For example, employee ID is a unique key. So any non-unique attribute, combined with employeeID attribute becomes a super key.

Examples of

One-to-one relationship:

1) Vehicle and registration – one vehicle has only one register number and one register number can be given to only one vehicle.

2) Similarly, a manager and department tables’ relationship. A manger can manage a single department and also a department can have a single manager.

One-to-many relationship:

Many-to-many relation:

 

 

Note: As per the Cramster rule, one question per post is to be answered. Please post the other in separate posts. 

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.