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.

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.

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

• NEW: Do not use any database name in the SQL script. The script must be able to create tables in any database it is executed in.
• Prepare sufficient testing data (at least 20 records or more for the largest table). Remember to include (cover) all special cases.
• Use the 3rd normal form (which means that you also have to create artificial keys for relation implementation).
• Important: You have to design a correct DB scheme (keep in mind the anomalies that might happen and eliminate them). If you end up with two entity tables and one relationship table, your design is wrong!
• The query part (getting information from a DB) must be solved by just ONE SQL request for each partial assignment.
• Effectively use the functions provided by the database (aggregation functions, calculations, etc.).
• The resulting SQL script should give proper results together with the data you provide. Therefore it should test the correct functionality of your script. The script is supposed to work with the data you provide (initialized from the saved file data.sql. In the comments state the expected output.
• Remember that a full application consists of multiple layers. You are implementing the storage layer. There is no need to set triggers nor use Pl/SQL or other complicated constructs that can easily be handled in the application layer. However, you should efficiently use the functionality of the DB server.
• In case of any ambiguity consult your lecturer. Solve eventual problems (and questions) in time.

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

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

• sum of final balances for all accounts of the customer specified, parameters: customer_first_name customer_family_name
• finds bank employee whose client performed the greatest debit transaction (no parameter)
• Sorts bankers according the number of accounts they lead (no parameter)
• finds user with the highest sum of transactions performed in a current month specified (you can include transaction between the same accounts twice), (no parameter)
• increases the initial account balance for all accounts maintained by a specific bank employee, parameters: 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:

• for a recipes of an author specified, parameter: author_name
• for a recipe that consumes the greatest amount of ingredients expiring before date specified (incl.). In case of ingredient number equivalency, print the alphabetically first one, parameter: date
• what needs to be purchased for a given recipe; print ingredient name together with amount required, parameter: food_name
• decreases the ingredient(s) amount in the fridge according to the recipe specified (from the fridge removes ingredients that were used in the food preparation), parameter: 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.

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:

• find all movies with the actor specified, parameter: actor_name
• find all movies with an actor born in the year specified, parameter: birth_year
• find all errors in the database (movies created before an actor involved was born) (no parameters)
• Sort producers according to the number of movies produced. (no parameters)
• Increase by 1 a year of production in the movies of the producer specified (simulates correcting a wrong input in the database). Parameter: 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:

• find all styles of a painter specified, parameter: artist_name
• find all painters that have painted a painting in the style specified, parameter: style
• find all paintings of the artist specified, parameter: artist_name
• find all paintings loaned in the date specified, parameter:date
• extends all loans of the gallery specified to a date specified, parameter: date

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

Create an SQL script that finds the following answers:

• find all calls by the operator specified, parameter: operator
• find current credit status (after all calls) for a specified customer (sum of all numbers), parameter: customer_name
• find user with maximum calls (sum of all calls from all numbers of the customer in minutes) (no parameter)
• find user that accepted the longest call from an operator, parameter: operator
• increases credit by 100 minutes for an operator specified, parameter: operator

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, ...

Create an SQL script that finds the following answers:

• find all orders from a manufacturer specified (print out product name and amount), parameter: manufacturer
• find sum of all products of a customer ordered since a date specified (incl.); sum all products together, parameter: customer_name date
• find a town with the maximum count of products sold (print out town name and total number of products), (no parameters)
• find a list of manufactures together with a count of cities (where it supplied), (no parameters)
• increases (by 10 pcs) number of ordered items for all orders for a customer specified, parameter: 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

Create an SQL script that finds the following answers:

• find a list of books borrowed by a specified reader, parameter: reader_name
• find a list of the most borrowed author, (no parameters)
• find all book exemplars that have been borrowed exactly n-times (to be repaired), parameter: count_borrowed
• Recommend a reader available books based on the most borrowed genre (by him) sorted by the popularity (number of loans), parameter: reader_name
• Extends all loans of the reader specified to the date specified. Parameters: reader_name date.

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