ER Case Studies
Case 1: A customer has at least one account. One account may belong to many customers. A transaction may be made against many accounts. One account may be connected to many transactions.
Case 2: A doctor’s chamber requires a database for effective doctor, patient and vaccine management. A doctor treats more than one patient. One patient can be treated by many doctors. One patient may take many vaccinations. One vaccine may be taken by many patients. Besides doctors there are also general employees in the chamber. One employee may work with many doctors. One doctor is assigned with many employees. One doctor is in charge of a many vaccines. One vaccine is under only one doctor.
Case 3: A database is required to keep track of employees, managers, their skills, projects assigned, and departments. An employee reports to only one department. One department may have many employees. One employee may work on many projects. A project may have many employees. An employee can have many skills. One skill may belong to many employees. One employee may manage other employees. One employee may have only one manager.
Case 4: There are members in bands. One member is member of exactly one band. One band must have at least one member. Bands release albums. An album is released by a band. A band may release as many albums they wish. A song may be included in many albums. One album must have at least one song. A member of the band works as a band leader of the band. Each band can have only one band leader. The date when a member is appointed as the leader of a band is also needs to be stored. A song is identified by a songid and can have a title and a category. Members’ identifiers are memberid and they have name, dateofbirth and age (is derived from dateofbirth). An album is identified by albumid and can have albumtitle and year released (can be multiple values if the album is re-released). A band is identified by bandid. A band also has bandname and yearestablished attributes.
DB Operations
Using the following case, answer next 5 questions. In a Customer Order Management database Customer ID, Name, Address and Contact Number are stored in Customer file. Product file contains Product ID, Product Name, Price and Stock. In the Order file Order ID, Date, Product ID and Customer ID are stored. |
The query - ‘Which product has highest stock?’ will require the following relational operation: A. Select & Project B. Project & Join C. Join & Select D. Join only |
The query - ‘List only customer ID and Customer Name of all customers?’ will require the following relational operation: A. Select only B. Project only C. Join D. None |
The query - ‘Which customer’s contact number is 000000000?’ will require the following relational operation: A. Select & Project B. Project & Join C. Join & Select D. Join only |
The query - ‘Which order has which products?’ will require the following relational operation: A. Select only B. Project only C. Join & Project D. Join & Select |
The query - ‘Order details of date 01 January 2008’ will require the following relational operation: A. Select & Project B. Project only C. Join & Select D. Join only |
0 comments:
Post a Comment