Table of Contents

SQL Database application implementation in Database Project: Task D

Before using the information here, ask your supervisor first. Also refer to the date of the last modification (see page bottom). Up to date for summer term of 2015/2016.

General information

This task is to be solved individually.

Choose one of the following variants and perform a reservation in the Upload System.

Your task is to design and create a database maintaining certain data (according to the specification) and to prepare SQL queries as stated below.

  1. Create an E-R diagram in one of the following notations:
    1. a scheme in Chen's notation without attributes (only entities and relations), and
    2. a scheme in Crow's Foot notation (with relations incl. attributes).
  2. Create an SQL script that creates empty tables (in an existing database). The script would contain only CREATE TABLE eventually DROP TABLE commands.
  3. Populate the databse with data and export them in the file data.sql. Use the following command: 
    mysqldump -u user --password=password database_name > data.sql
    You can anytime import the data as follows: 
    mysql -u user --password=password database_name < data.sql
  4. Implement SQL script that allows to answer the following questions (see relevant specification below).
  5. After successful defense, upload your work into the Upload System.

Common requirements

Please follow the following common requirements for implementation (valid for all variants):

Available variants

Variant 1: Bank accounts

Create a database for maintaining bank accounts (in a bank). You are expected to maintain information about customers (account holders), accounts (any customer can have multiple accounts), deposits, drafts, transactions between accounts, bank employees and bank subsidiaries. An employee is assigned to a particular subsidiary. Each customer is assigned exactly one bank employee.

The information that must be maintained for account database are the following 

first_name, family_name, account_number, initial_account_balance, bank_employee_first_name, bank_employee_family_name, bank_subsidiary_name, bank_subsidiary_address

You can assume that the customer is fully identified with his first and family name.

The transaction data is the following: 

source_account_number, destination_account_number, date, transaction_amount
where the transaction_amount can be positive or negative. The date format is 'YYYY-MM-DD'. If one of the account numbers is 0, it is a deposit/draft.

Create an SQL script that finds the following answers in the database:

Variant 2: Cook book

Create a cook book database. Each recipe consists of defined amount of a few ingredients. Maintain information about the current state of ingredients in a refrigerator, their expiry date and purchase place.

Recipe data contains the following information: 

food_name, recipe_author, ingredient_1, amount_1, ingredient_2, amount_2, ingredient_3, amount_3, ingredient_4, amount_4, ...

The ingredients in the fridge are defined by: 

ingredient, amount, expiry_date, purchase_place

Create an SQL script that queries the database:

Note: Food name, ingredient name and purchase place name can contain spaces.

One ingredient can be sold in multiple places, purchase place sells multiple ingredients. Author name consists of author_fist_name and author_family_name.

Variant 3: A movie database

Design a movie database. Together with each movie maintain information about actors (multiple for each movie), producer, author and genre.

Movies have the following properties: 

 
movie_name, year, genre, producer, author, actor1, actor2, ...

Actors have the following information: 

actor_name, birth_year

Create an SQL script that finds the following answers in the database:

Note: The movie names and actor names can contain spaces. Movie can contain any number of actors.

Design a database of paintings and evidence of their loans into galleries. A painting is painted by one painter in one style. Galleries can borrow paintings for a certain period (from–to).

Paintings are represented by the following information: 

painting_name, artist_name, style, year

Loans are specified by the following information: 

painting_name, gallery_name, date_from, date_to

Painter names can be stored in one attribute (both first and family name). You can assume that painting names are unique.

Create an SQL script that finds the following answers:

Note: The painting, gallery and artist names can contain spaces.

Variant 5: Phone calls

Create a database for phone call evidence. Customer can have multiple phone numbers. Each number has a certain operator and tariff.

Phone numbers are represented by the following information: 

name, phone_number, operator, tariff, initial_credit_in_minutes
where name (first name and family name) uniquely identifies the person.

Call information are represented by the following information: 

number_caller, number_receiver, duration_minutes

Create an SQL script that finds the following answers:

Note: Operator, customer and tariff names can contain spaces.

Variant 6: List of orders

Design a database for customer evidence and evidence of their orders. Order contains multiple items and each item has a product type and amount specified. You can assume that the product assortment is limited.

Customers are represented by the following information: 

name, town, street, house_number

Orders are represented by the following information: 

order_code, transaction_date, customer_name, product1_name, product1_amount, product1_manufacturer, product2_name, product2_amount, product2_manufacturer, ...

Create an SQL script that finds the following answers:

Note: The customer, town and street names can contain spaces.

Variant 7: Library

Create a database for evidence of books and their loans. Maintain information about readers (clients), books and their loans. Each book can be in multiple exemplars in the library.

Books are represented by the following data: 

book_name, author, year, genre, exemplar_number

The loans are represented by the following information: 

 
book_name, exemplar_number, reader_name, date_loaned, date_returned
If the book has not been returned yet, the date_returned is not specified.

Create an SQL script that finds the following answers:

Note: The book names can contain spaces. The database must be in the 3rd normal form.