Exercise 1:
Create a crow's foot ERD for each of the following descriptions. (Note: The word "many" merely means "more than one" in the database modeling environment.)
a. Each of the MegaCo Corporation's divisions is composed of many departments. Each of those departments has many employees assigned to it, but each employee works for only one department. Each department is managed by one employee, and each of those managers can manage only one department at a time.
b. During some period of time, a customer can rent many videotapes from the BigVid store. Each of the BigVid's videotapes can be rented to many customers during that period of time.
c. An airliner can be assigned to fly many flights, but each flight is flown by only one airliner.
d. The KwikTite Corporation operates many factories. Each factory is located in a region. Each region can be "home" to many of KwikTite's factories. Each factory employs many employees, but each of those employees is employed by only one factory.
Exercise 2:
Suppose that you are using the following a database composed of the two tables shown in
Table name: DIRECTOR Database name: EXERCISE2_QUESTION
| DIR_NUM |
DIR_LNAME |
DIR_DOB |
| 100 |
Broadway |
12-Jan-65 |
| 101 |
Hollywoody |
18-Nov-53 |
| 102 |
Goffy |
21-Jun-62 |
Table name: PLAY
| PLAY_CODE |
PLAY_NAME |
DIR_NUM |
| 1001 |
Cat on a cold, bare roof |
102 |
| 1002 |
hold the mayo, pass the bread |
101 |
| 1003 |
I never promissied your coffee |
102 |
| 1004 |
silly putty goes to washington |
100 |
| 1005 |
see no sound, hear no sight |
101 |
| 1006 |
starstruck in biloxi |
102 |
| 1007 |
stranger in parrot Ice |
101 |
a. Identify the primary keys.
b. Identify the foreign key.
c. Draw the Entity Relationship model.
d. Draw the relational schema to show the relationship between DIRECTOR and PLAY.
e. Suppose you wanted quick lookup capability to get a listing of all the plays directed by a given director. What table would be the basis for the index table, and what would be the index key?
e. What would be the conceptual view of the index table described in part e? Depict the contents of the (conceptual) index table.
Exercise 3
Create an ERD based on the Crow's Foot model ( Database Schema), using the following requirements.
- An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative.
- The INVOICE is written for a single CUSTOMER. However, each customer may have many invoices.
- An INVOICE may include many detail lines (LINE), which describe the products bought by the customer.
- The product information is stored in a PRODUCT entity.
- The product's vendor information is found in a VENDOR entity.
Exercise 4: NORMALISATION
The dependency diagram in Figure Q7.2 indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book.
Figure - Book royalty dependency diagram
a. Based on the dependency diagram, create a database whose tables are at least in 2NF, showing the dependency diagram for each table.
b. Create a database whose tables are at least in 3NF, showing the dependency diagram for each table.
- Re-name the RicardoCars database as follows.
o RicardoCars -StudID-Lastname , where StudID is your student number.
Write both your Stud_ID and Lastname as it is a group of 2 students assignment.
RicardoCars is a company that builds model cars of popular classic and new cars such as Chevrolet, Ford, Porsche, Ferrari, Dodge, Jaguar, Mercedes, Plymouth, Ferrari and Lamborghini. There are four mechanics who work in this company.
This company has developed a database to keep a track of the details of the cars and mechanics who worked there.
This database has two tables.
- Mechanic - this table has some details about the mechanic
- Catalog - this table has the details of the cars
1. Start Access application and open the RicardoCars.mdb database (this is the database which you have already renamed).
2. Study the two tables in the database. Double click on each table and use "design view" to study the table design. Double click on each table and use "data sheet view" to study the details of the records.
3. Mechanics are paid depending on the Type of the car they make (Type is a field in the Catalog table) and how many cars they make. For example, for Ferrari and Jaguar, rate per car is $3.50 and $3.00 respectively.
The following are the Rate (per car) for different car types.
|
Type of car
|
Rate per car in $
|
|
Dodge
|
2.00
|
|
Ferrari
|
3.50
|
|
Jaguar
|
3.00
|
|
Mercedes
|
3.75
|
|
Porsche
|
3.25
|
4. Go to the "design view" of Catalog table. Insert a new row just below "Type" field. Make sure that the field data type and size are correct . Name this new field as "Rate". (2 marks)
5. Go to data sheet view of Catalog table. You will now see an empty column with the field name "Rate". Fill up the "Rate" column with relevant values. (relevant values of Type and Rate are given in the table at (c) ) (2 marks)
NOTE: Close ALL the tables when you are creating queries. Use SQL in your queries
6. Create a query (using SQL in Access) that will select all the car types of which the price is less than $40. The query should display all of the fields of the catalog table. Sort the results. Run the query. Save the query as Less than 40. (5 marks)
7. Create a query (using SQL in Access) that lists all the cars that the mechanic M102 had worked on. The query should display the type of the car, model, price, mechanic ID and mechanic name. Save the query as MechanicM102. (5 marks)
8. Mechanics are paid using the following formula.
Pay = Rate * QuantityMade
Create a calculated field called Pay in a new query. Include all the fields from the Catalog table in the query. Sort the results. Run the query. Save this query as CarPayment (6 marks).
9. Create a columnar form that can be used to enter and update all the details of the Catalog table. Use any style that you wish for the form. A maximum mark is allocated to the best designed form. Save the form as CatalogUpdate. (2 marks)
10. Create a report containing the CarID, the Type, the Model, the QuantityMade and the MechanicalName. Save the report as MechanicReport. (2 marks) Use any style that you wish.
11. Save your solution in your USBs and save one copy in your drive (backup copy) when completed.