Database Exercises

Software and System Design
Spring 2020
  1. A TV company wishes to develop a database to store data about the TV series that the company produces. The data includes information about actors who play in the series, and directors who direct the episodes of the series.

Actors and directors are employed by the company. A TV series are divided into episodes. Each episode may be transmitted at several occasions. An actor is hired to participate in a series, but may participate in many series. Each episode of a series is directed by one of the directors, but different episodes may be directed by different directors.
Examples of database queries:

    • Which actors play in the series Big Sister?
    • In which series does the actor Bertil Bom participate?
    • Which actors participate in more than one series?
    • How many times has the first episode of the series Wild Lies been transmitted? At what times?
    • How many directors are employed by the company?
    • Which director has directed the greatest number of episodes?

Develop an E/R model of this system. Find attributes of the entity sets. Determine which of the attributes that can be used as primary keys.

  1. Consider the following company DB. It stores information about employees (identified by SIN, with salary and phone attributes) and departments (identified by dept ID, with department name and budget as attributes). Employees work in departments. The DB records the interval during which an employee works for a department. Draw an ER diagram that describes each of the following situations.

a. Employee is not allowed to work in a department for two or more intervals.

b. Employee is allowed to work in a department for one or more intervals.

  1. Consider the following ER diagram.

a. List all primary keys and foreign keysfor entities sets Students and Courses, and for relationship set Enrolled.

b. Show the table (e.g. table_name(fields...) )

ER_Diagrams.png

4. Shops sell items at varying prices. Customers buy items from shops. This is described by the following relations:

    • Shops(shopId, name, address)
    • Items(itemId, name, description)
    • Sells( shopId, itemId , price)
    • Customers(customerId, name, address)
    • Sales(saleId, customerId, itemId, shopId, date)
(Underline - key, italics - foreign key)

Draw in ER diagram. You may use https://www.lucidchart.com/

solutions

Topic attachments
I Attachment History Action Size Date WhoSorted ascending Comment
PNGpng ER_Diagrams.png r1 manage 14.0 K 2020-02-03 - 14:48 JimSkon  

This topic: Main > WebHome > SoftwareSystemDesign2020 > DatabaseDesignProblemSSD
Topic revision: r2 - 2020-02-03 - JimSkon
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback