A. Short Answers
1. Describe how to represent a multivalued attribute of an entity in deigning logical database table(s). Give an example other than one in this chapter. Show your table(s) with your explanation. Read textbook for the example.
Describe how to represent a M:N recursive relationship in designing database table(s). Give an example other than one in this chapter. Show your tables with explanation. Read textbook for the example
For a one-to-many relationship between tables of Director and Project , if the possible minimal cardinalities are shown in min cardinality and data integrity question sheets.docx , filling in your answers into the 3rd column of the sheets (2,3,4) to show your understanding about how to ensure the referential data integrity when a record is inserted, deleted or modified (key value). (Watch the video lecture on this question. The chapter 6 of text also shows the similar sheets to illustrate how the referential integrity is enforced),
4. What is a trigger ?(read Chapter 6) How can triggers be used to enforce data integrity? Show an example (note, you may use the answer you have for question A (3))
5. (Read beginning of Chapter 3 Functional Dependency)
5.1 What is the Functional Dependency (FD)? Give an example using the notation of FD X-> Y.
5.2) Assuming MSU database has a table such as Student (SSN , name, birthdate, GPA, school_name, dean_of_ the _school, dean_phone). Based on your understanding to the situation of the university, list all Functional Dependencies implied by this table. (using the notation X- > Y).
5.3) (Bonus 1 point) What the problems does above Student table have? What is your solution to the problem you identified.
B. Data Modeling and logical DB design
San Juan Sailboat Charters is an agency that leases sailboats to customers for a fee. San Juan does not own any sailboats; it leases boats on behalf of the owners who wish to earn income when they are not using their boats. San Juan charges boat owners fees for its service. San Juan specializes in boats that can be used for multi-day or weekly charters—the smallest sailboat in its inventory is 28 feet and the largest is 51 feet. For each board, San Juan keeps a contract with its owner which states the fee, duration of the contract, date of sign, etc.).
A customer who leases a boat needs to sign a leasing document that states information about the boat and customer, term of lease, itemize/total charges, destination, etc. Each sailboat is fully equipped at the time of lease. Most of the equipment are provided by the owners, but some are added by San Juan. The owner-provided equipment includes what is fixed on the boat, such as radios, compasses, depth indicators and other instrumentation, stoves, and refrigerators. Other owner-provided equipment is not installed as part of the boat. Such equipment includes sails; lines; anchors; dinghies; life preservers; and (in the cabin) dishes, silverware, bedding, and the like. San Juan provides consumable which can also supplies such as charts, navigation books, soap, dishtowels, toilet paper, and similar items.
An important part of San Juan’s responsibilities is keeping track of the equipment and supplies on the boat. Customers are responsible for all equipment during the period of their charter. San Juan likes to keep accurate records of its customers and their leases, not only for marketing but also for recording the trips that customers have taken. Some itineraries and weather conditions are more dangerous than others, so San Juan likes to know which customers have what experiences.
Most of San Juan’s business is bare-boat chartering, which means that no crew is provided. In some cases, however, customers request the services of crew members, so San Juan hires such personnel on a part-time basis.
Sailboats often need maintenance. San Juan is required by its contracts with the boat owners to keep accurate records of all maintenance activities and costs, including normal activities (for example, cleaning or engine-oil changes) and unscheduled repairs. A boat engine, for example, might fail while the boat is far away from San Juan’s facility. In this case, the customers radio the San Juan dispatcher, who determines the best facility to make the repair and sends the facility’s personnel to the disabled boat. To make these decisions, the dispatchers need information about repair facilities as well as past histories of repair quality and costs.
To promote its business, San Juan Sailboat Charters hosts boat-fishing tournaments a few times a year. Only customers who currently lease its boards are allowed to participate. The management of the company needs to keep track of tournament information such as title of tournament, date, reward type, the boats and customer involved, and the name of gold medalist, etc.
Another promotion activity San Juan Sailboat Charter does is to host the boat show a few time a year. During the show time, the company will display and demonstrate features of some of its boats. The company wants to keep track of information about this type of event, such as show’s location, show time period, the budget and the cost of the show, etc.
1. Create an ER model for above situation.
(Note: Suppose that San Juan has hired you to construct a database for them. To do this, you decide to create a data model. You know you will need to interview users and gather forms, reports, and other requirements. Before you do this, however, you decide to construct a trial data model. You are hoping that this model will help you determine specific questions that you will need to ask San Juan Sailboat personnel.)
2. Convert this E/R model into relational database design with the notation show in chapter 3 (For example: an employee table can be expressed as Employee(ssn, name, address, dept_no) )
please use the example below for the tables!