1. A database is being constructed to store information about patients in a hospital. On arrival, each patient's personal details (name, address, and telephone number) are recorded where possible, and they are given an admission number. They are then assigned to a particular ward (Accident and Emergency, Cardiology, Oncology, etc.). In each ward, there are a number of doctors and nurses. A patient will be treated by one doctor and several nurses over the course of their stay, and each doctor and nurse may be involved with several patients at any given time.
Identify the entities, attributes, relationships, and cardinality ratios from the description Draw an entity-relationship diagram (using Sybase Power Designer) showing the items you identified.
2. A Bus Company owns a number of buses. Each bus is allocated to a particular route, although some routes may have several busses. Each route passes through a number of towns. One or more drivers are allocated to each stage of a route, which corresponds to a journey through some or all of the towns on a route. Some of the towns have a garage where buses are kept and each of the busses are identified by the registration number and can carry different numbers of passengers, since the vehicles vary in size and can be single or double-decked. Each route is identified by a route number and information is available on the average number of passengers carried per day for each route. Drivers have an employee number, name, address, and sometimes a telephone number.
Identify the entities, attributes, relationships, and cardinality ratios from the description Draw an entity-relationship diagram (using Sybase Power Designer) showing the items you identified.
3. Use the contents of Figure 2.3 (Slide #22 of your PDF Lesson Resources) to work through questions below
- Write the business rule(s) that govern the relationship between AGENT and CUSTOMER.
- Given the business rule(s) you wrote in Problem 1, create the basic Crow's Foot ERD.