Search
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.
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.
CREATE TABLE
DROP TABLE
data.sql
mysqldump -u user --password=password database_name > data.sql
mysql -u user --password=password database_name < data.sql
Please follow the following common requirements for implementation (valid for all variants):
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
0
Create an SQL script that finds the following answers in the database:
customer_first_name customer_family_name
bank_emploayee_first_name bank_employee_family_name
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:
author_name
date
food_name
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.
author_fist_name
author_family_name
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
actor_name
birth_year
producer
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:
artist_name
style
Note: The painting, gallery and artist names can contain spaces.
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
Call information are represented by the following information:
number_caller, number_receiver, duration_minutes
operator
customer_name
Note: Operator, customer and tariff names can contain spaces.
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, ...
manufacturer
customer_name date
customer name
Note: The customer, town and street names can contain spaces.
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
date_returned
reader_name
count_borrowed
reader_name date
Note: The book names can contain spaces. The database must be in the 3rd normal form.