ERD CASE STUDIES-EXAMPLES Database Management System
ERD CASE STUDIES-EXAMPLES Database Management System
ERD CASE STUDIES-EXAMPLES
ERD Notation: There are two type of notation used in ERD :
- Peter Chen notation;
- Bachman notation.
Not surprisingly, Peter Chen and Bachman are the name inventors of the notation. The following table
gives the notation.
Fig. Example for Peter Chen Notation
Given below are a few examples of ER diagrams using Bachman notation. First the textual statement is given followed by the diagram.
- In a Company, each division is managed by only one manager and each manager manages only one division
- Among the automobile manufacturing Companies, a Company manufactures many Cars, but a given car is manufactured in only one company.
- In a college, every student takes many courses and every course is taken by many students.
(a) Country Bus Company
A Country bus Company owns a number of buses. Each bus is allocated to a particular route, although Some routes may have Several buses. 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 buses 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.
- Bus – Company owns buses and will hold information about them.
- Route – Buses travel on routes and will need described.
- Town – Buses pass through towns and need to know about them.
- Driver – Company employs drivers, personnel will hold their data.
- Stage – Routes are made up of Stages. Garage –
- Garage houses buses, and need to know where they are.
- A bus is allocated to a route and a route may have several buses.
- Bus-route (m:1) is serviced by a route comprises of one or more stages.
- Route-Stage (1:m) Comprises One or more drivers are allocated to each stage.
- Driver-stage (m:1) is allocated A Stage passes through some or all of the towns on a route.
- Stage-town (min) passes-through. A route passes through some or all of the towns.
- Route-town (min) passes-through Some of the towns have a garage.
- Garage-town (1:1) is situated Agarage keeps buses and each bus has one home garage.
- Garage-bus (m:1) is garaged.
Draw ER Diagram
Fig. Bus Company
- Bus (reg-no, make, size, deck, no-pass)
- Route (route-no avg-pass)
- Driver (emp-no, name, address, tel-no)
- Town name)
- Stage (stage-no)
- Garage (name, address)
(b) A Crop farm Example
Rohit’s family owns and operates a 640-acre farm for several generations. Since the farm business is growing, Rohit is thinking to build a database that would make easier the management of the activities in the farm. He is considering the following requirements for the database:
- For each livestock classification group (for example, cow, horse etc.), Rohit keeps track of the following: identification number, classification, total number of livestock per classification group (for example, number of cows, number of horses etc.).
- For each crop the following information is recorded Crop identification number and classification.
- Rohit has recorded the yield of each crop classification group during the last ten years. The records consist of the year, yield, sales, price of the crop and the amount of money earned.
- Rohit has recorded the yield of each livestock classification group during the last ten years. The records consist of the following historical data: the year, (historical) selling price per head, number of livestock in the end of the year, number of livestock sold during one-year period, and the total amount of money earned.
Draw an E-R diagram for this application. Specify the key attribute of each entity type.
(c) Airline Example
Major airlines companies that provide passenger services in Taiwan are: UniAir, TransAsia Airways, Far Eastern Transport, Great China Airlines etc. Taiwan’s Federal Aviation Administration (TFAA) keeps a database with lots of information on all airlines. This information is made accessible to all airlines in Taiwan with the intention of helping the Companies assess their Competitive position in the domestic market. The information kept consists of:
- Each airpline has an identification number, name of the contact person and telephone number.
- For each aircraft identification number, capacity and model is recorded.
- Each employee has an employee identification number, name, address, birthday, sex, position with the company and qualification.
- Each route has a route identification number, origin, destination, classification (into domestic or international route), distance of the route and price charged per passenger.
- Each airline keeps information about their buy/sell transactions (for example, selling an airplane ticket is a sell transaction, paying for maintenance is a buy transaction). Each transaction has a transaction identification number, date, description and amount of money paid/received.
Draw an E-R diagram for the database presented above. Make Sure to identify the associative entity (entities) and provide corresponding key attribute (attributes).
(d) Automobile Distribution Center
Ford distribution centers provide automotive parts to authorized dealers and the dealers distribute the parts to Customers throughout North America. Ford is faced with pressure to provide excellent Customer Service at minimum Cost. Maintaining a well organized database of information, will contribute to achieving this goal.
Ford keeps the following information about each of its distribution centers: identification number, location (X longitude coordinate and Y latitude coordinate), address (city, state, zip code) and name of the Contact person. The following information is kept about each dealer: identification number, dealer’s location (X longitude Coordinate and Y latitude Coordinate), address (name of the city, State, zip Code), name of the Contact person and the identification number of the primary distribution centers serve this dealer.
A distribution center sends a shipment to its dealers every week. The distribution centers keep the following information about each shipment the date, an identification number of the product shipped, an identification number of the dealer that receives the shipment.
The following information is kept about each product: a product identification number name, price, weight, value.
Ford keeps the following information about the flow and cost data for all distribution centers to dealer channels: distribution center identification number, dealer identification number, product identification number, the number of miles between each distribution center and its dealers using the road network, the quantity of products being shipped and the dollar value of the shipment.
Draw an ER diagram for the Ford database.
(e) Housing Society Example
The University Housing Office receives many applications from graduate and married students requesting an apartment on campus. The housing villages are sited in five different locations and each village has about 500 apartments. Each apartment falls into one of the apartment categories. An apartment category is determined based on village location, whether the apartment has dish washer or not, a single or double bedroom, central or window unit air conditioner, furniture or not.
Housing officekeeps the following information about the Current residents: the SSN of the family head, name, address, telephone number, marital status, the name of the major college and department in which the family head is enrolled in.
Housing office keeps the following information about the students that have applied for in campus housing, but have not been assigned to an apartment yet: SSN of the applicant, name, address, telephone number, marital status, the name of the major college and department in which the family head is enrolled in, the preference about the apartment category.
Draw an ER diagram for the University Housing Office database. State any assumptions you need to make in order to develop a compete diagram.
(f) institution Example
This database includes information about the course timetable of an academic institution. For each Course the following information is recorded an identification number, name of the course, name of the teacher assigned to teach the course, the number of periods each week it will be taught, the number of
For each teacher the following information is recorded: SSN, name, name of the department he/she works with, skills, the yearly salary. For each class period the following information is recorded: period number, starting time, ending time. For each room the following information is recorded: room number, room type (classroom, office, auditorium, and computer lab), capacity.
The above information is used in order to make the right assignment of a teacher that teaches a particular course to a time period and a classroom. Draw an E-R diagram for this database. State clearly any assumptions you make.
Some Questions with few Answers
DRAW ERD FOR THE FOLLOWING
A publishing company produces scientific books on various subjects. The books are written by authors who specialize in one particular subject. The company employs editors who, not necessarily being specialists in a particular area, each take sole responsibility for editing one or more publications. A publication covers essentially one of the Specialist Subjects and is normally written by a single author. When writing a particular book, each author works with on editor, but may submit another work for publication to be Supervised by other editors. To improve their competitiveness, the company tries to employ a variety of authors, more than one author being a specialist in a particular subject.
A general hospital consists of a number of specialized wards (such as Maternity, Paediatry, Oncology, etc). Each ward hosts a number of patients, who were admitted on the recommendation of their own GP and Confirmed by a consultant employed by the Hospital. On admission, the personal details of every patient are recorded. A separate register is to be held to store the information of the tests undertaken and the results of a prescribed treatment. A number of tests may be conducted for each patient. Each patient is assigned to one leading Consultant but may be examined by another doctor, if required. Doctors are specialists in some branch of medicine and may be leading consultants for a number of patients, not necessarily from the same ward.
A database is to be designed for a Car Rental Co. (CRC). The information required includes a description of Cars, Subcontractors (i.e., garages), company expenditures, Company revenues and customers. Cars are to be described by such data as make, model, year of production, and engine size, and fuel type, number of passengers, registration number, purchase price, purchase date, rent price and insurance details. It is the company policy not to keep any car for a period exceeding one year. All major repairs and maintenance are done by Subcontractors (i.e., franchised garages), with whom CRC has long-term agreements. Therefore the data about garages to be kept in the database includes garage names, addresses, range of Services and the like. Some garages require payments immediately after a repair has been made, with others CRC has made arrangements for Credit facilities. Company expenditures are to be registered for all outgoings connected with purchases, repairs, maintenance, insurance etc. Similarly, the cash inflow coming from all Sources – Car hire, car sales, insurance claims – must be kept of file. CRC maintains reasonably stable client base. For this privileged category of customers special credit card facilities are provided. These customers may also book in advance a particular car. These reservations can be made for any period of time up to one month. Casual Customers must pay a deposit for an estimated time of rental, unless they wish to pay by Credit card. All major credit cards care accepted. Personal details (such as name, address, telephone number, driving license number) about each customer are kept in the database.
A database is to be designed for a college to monitor students’ progress throughout their Course of study. The students are reading for a degree (such as BA, BA (Hons) MSc, etc.) within the framework of the modular system. The college provides a number of module, each being characterized by its Code, title, Credit value, module leader, teaching staff and the department they come from. A module is co-ordinated by a module leader who shares teaching duties with one or more lecturers. A lecturer may teach (and be a module leader for) more than one module. Students are free to choose any module they wish but the following rules must be observed some modules require pre-requisites modules and some degree programmes have compulsory modules. The database is also to contain some information about students including their numbers, names, addresses, degrees they read for and their past performance (i.e., modules taken and examination results).
A relational database is to be designed for a medium sized company dealing with industrial applications of Computers. The Company delivers various products to its customers ranging from a single application program through to complete installation of hardware with Customized software. The company employs various experts, Consultants and Supporting staff. All personnel are employed on long-term basis, i.e., there are no short-term or temporary staffs. Although the Company is somehow structured for administrative purposes (that is, it is divided into departments headed by department managers) all projects are carried out in an inter-disciplinary way. For each project a project team is selected, grouping employees from different departments and a project manager (also an employee of the company) is appointed who is entirely and exclusively responsible for the control of the project, quite independently of the company’s hierarchy. The following is a brief Statement of Some facts and policies adopted by the company.
Explain the distinctions among the terms primary key, candidate key, and Superkey :
- A superkey is any set of attributes such that the values of the attributes (taken together) uniquely identify one entity in the entity set.
- A candidate key is a minimal superkey – a superkey with no redundant attributes. In other Words, if any one of the attributes is removed, the set of attributes that remain no longer form a superkey.
- A primary key is one of the candidate keys, designated by the database designer.
- Every primary key is also a candidate key; every candidate key is also a superkey, but not vice Versa.
Construct an ER diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents
Construct an ER diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.
Construct an E-R diagram for the registrar’s office. Document all assumptions you make about the mapping Constraints.
- A class meets only at one particular place and time. This diagram does not attempt to model a class meeting at different places or at different times
- There is no guarantee that the database does not have two classes meeting at the sameplace and time
- Each class has a unique instructor
Consider a database used to record the marks that students get in different exams of different course offerings.
(a) Construct an ER diagram for the database modeling exams as entities and using a ternary relationship
(b) Construct an alternative ER diagram that uses only a binary relationship between students and course-offerings. Make sure that only one relationship exists between a particular student and course-offering pair, yet you can represent the marks that a student gets in different exams of a course offering