IFSM 410 Exercises and Case Studies

1. EXERCISES
1. Construct an Entity-Relationship diagram for the following situation:
   A company consists of several dept, each having a number of employees. Each dept has a manager who must be on the monthly payroll. Other employees are on either the monthly payroll or the weekly payroll, and are members of the sports club if they so wish.
2. CASE STUDIES
1. PC Associates is a large, multinational consulting corporation, which uses a number of PCs and software packages in the course of her work. PC Assoicates needs to maintain the following information about the PCs and software packages its employee uses to do their jobs. For each PC, the computer number, the computer model and manufacturer, and the type of microprocessor needed to be stored. Data for each employee also needed to be stored - such as the employee name, employee number, and phone number. Also for each PC, the inventory tag number, location, computer number and the number of the employee who owns the PC. For each software package, the package number, current cost, type, name and version needed to be stored. Also for each software package installed on a PC, the package number and cost, installation date and inventory tag number needed to be stored.
   
   Analyse and create the database structure for PC Associates. Questions for discussion will be distributed in class.
   
   
2. You are a systems analyst. You have been assigned to design a database management system (DBMS) for Mechanical Engineering (ME), Inc. ME orders its parts from two types of vendors--local and nonlocal vendors. Vendors are tracked by vendor #, n ame, address, and telephone #. Parts are ordered by means of a purchase order. Local vendors can supply both assembled and sub-parts, while nonlocal vendors can supply only subparts. For each order, ME would like keep track of information such as order #, quantity ordered, date requested and date shipped. A single part is ordered on each purchase order. A part can be an assembled part or a sub-part. An assembled part can contain two or more sub-parts and each sub-part can be used in two or more assembled parts. Parts should have: part #, description, and cost.
   
   ME has requested that the DBMS that you design should provide capability to list the sub-parts that make up an assembled part and from which vendor it is ordered.
   
   Given the above, draw an enity-relationship diagram and translate it into relational tables, for example: student (student id, name, address, major, dept.), etc.
   
   
3. You are a system analyst. You have been assigned to design a database management system for Movies Rental, Incorporated (MRI). MRI gathers and organizes facts on movies, directors, and movie stars.
   When she adds a new movie to her files, she assigns it a number that she uses for reference purposes. In addition to this number, she keeps the title of the movie; the type of movie; the year it was produced; the critic's rating (number of stars awarded); the rating of the MPAA(Motion Picture Association of America); the number of Academy Award nominations it received; and the number of Academy Awards it won.
   Similarly, she assigns a number to the directors she adds to the files. She also keeps the director's name, the year in which he or she was born and, if appropriate, the year in which he or she died. Actors and actresses are likewise assigned numbers when they are added to the files. Also filed are the stars' name, birthplace, the year born, and again, if appropriate, the year died.
   The DBMS should be able to provide the following information:
1. For each movie, list its number, title, the number and name of its directors, the critics' rating, the MPAA rating, the number of awards the movie was nominated for, and the number it won.
2. For each movie, list its number and title, along with the number and name of all the stars who appeared in it.
3. For each movie star, list his or her number and name, along with the number and name of all the movies in which he or she starred.