ER Diagrams for Various Database Systems

1. Extra EER

2. Q1: Draw an ER diagram A construction company wishes to establish a database system to record information about employees. The employee data to be recorded in the database are consists of employee SINs (which are unique), first names, last names and home phone numbers. The company has three types of special employees; as well as many regular employees who do not fit into one of these categories, which are:  For insured employees; the insurance policy number, the insured amount, and the annual premium are to be recorded.

3.  For qualified; the professional qualification and the annual institute fees are to be recorded.  For managers; the number of profit shares, and the parking stall number are to be recorded. In addition, they need to record data about departments; each (unique) department name, budget, and location should be recorded. Employees must work for one, and only one department, departments must have at least one employee, and may (of course) have more than one.

4. Each manager normally manages one department, but on occasion a manager may be responsible for more than one department, but never less than one. A department can have only one manager, but occasionally will not have a manager. It is necessary to record the dependents of insured employees; the first name, age and relationship (e.g. child, spouse, etc.) of each dependent must be recorded. It is assumed that no two dependents of the same employee will have the same name. An insured employee must have at least one dependent, the dependents must have one and only one insured employee.

7. Q2  Q1: Consider the following information about a university database:  Professors have an SSN, a name, an age, a rank, and a research specialty.  Projects have a project number, a sponsor name, a starting date, an ending date, and a budget.  Graduate students have SSN, a name, an age, and a degree program (e.g., M.S. Or Ph.D.).  Each project is managed by one professor (known as the project’s principal investigator).  Each project is worked on by one or more professors (known as the project’s coinvestigators).  Professors can manage and/or work on multiple projects.

8. Q2  Each project is worked on by one or more graduate students (known as the project’s research assistants).  When graduate students work on a project, a professor must supervise their work on the project.  Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one.  Departments have a department number, a department name, and a main office.  Departments have a professor (known as the chairman) who runs the department.

9. Q2  Professors work in one or more departments, and for each department that they work in, a time percentage is  associated with their job.  Graduate students have one major department in which they are working on their degree.  Create an ER/EER diagram that fulfils the above requirements and motivate your suggestions.

11. Q3  : Draw EER diagram for a training center, including information about its members, training activities and bookings. Each member is identified through his/her e-mail address. Gold-members can book any training activity, while common members can only book core activities. For each training activity, the database stores the schedule (week, week day, and time), the room, the leader and the e-mail of the leader. Each leader leads several activities per week, but the same activities every week. Translate the EER diagram into a relational model. Mark each primary key with a straight underline, and each foreign key with a dotted underline. For the foreign keys, mark the attributes that are referred by the foreign keys by means of arrows.

13. q4  A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.

15. q5  Draw an EER diagram for the following application: to keep track of information for an art museum. Discuss any assumptions you make.  The museum has a collection of ART_OBJECTS. Each ART_OBJECT has a unique Id_no, an Artist, a Year, a Title, and a Description. The art objects are categorized in several ways, as discussed below.  ART_OBJECTS are categorized based on their type. There are three main types: PAINTING, SCULPTURE, and STATUE, plus another type called OTHER to accommodate objects that do not fall into one of the three main types.  A PAINTING has a Paint_type (oil, watercolor, etc.), material on which it is Drawn_on (paper, canvas, wood, etc.), and Style (modern, abstract, etc.).  A SCULPTURE or a statue has a Material from which it was created (wood, stone, etc.), Height, Weight, and Stile.  An art object in the OTHER category has a Type (print, photo, etc.) and Style.

16.   ART_OBJECTs are categorized as either PERMANENT_COLLECTION (objects that are owned by the museum) or BORROWED. Information captured about objects in the PERMANENT_COLLECTION includes Date_acquired, Status (on display, on loan, or stored), and Cost. Information captured about BORROWED objects includes the Collection from which it was borrowed, Date_borrowed, and Date_returned.  The museum keeps track of ARTIST information, Name, DateBorn , Date_died, Country_of_origin, Epoch, Main_style, and Description. The Name is assumed to be unique.

17.  Different EXHIBITIONS occur, each having a Name, Start_date, and End_date. EXHIBITIONS are related to all the art objects that were on display during the exhibition.  Information is kept on other COLLECTIONS for borrowed art, including Name (unique), Type (museum, personal, etc.), Description, Address, Phone, and Current Contact_person.

19. S/G notation (chen notation)