1. Revise your design, if necessary.

2.Translate your ER diagram into Relational Schema

Write up a relational schema that is equivalent to your ER diagram. Make sure that you translate not only entities but also relationships. Write a short explanation for each relation. For example,

Orders

Orders(ordernum: INTEGER, cid: INTEGER, cardnum: CHAR(16), cardmonth: INTEGER, cardyear: INTEGER, order_date: DATE, ship_date: DATE)

This table stores one entry for each order the user places. It only includes general information about the transaction itself, and not the specific items purchased by the user. The cid of the user placing the order as well as their credit card number and credit card date are stored here. In addition, we can track when the order was submitted as well as when it shipped.

3.Implementation of the Schema in MariaDB

Write an SQL script with the commands to create the tables of your database.

The CREATE TABLE statements must specify: o Appropriate types for the attributes;

• The primary key; o Constraints such as NOT NULL and UNIQUE whenever appropriate; o Default values where appropriate;
• FOREIGN KEY constraints, together with the policy for reacting to changes (remember that the default is ON DELETE NO ACTION)

Write comments into the script that explain the rationale behind the definition of your constraints.

4.Loading Data

Load interesting data into the database. In principle, you can do this either by writing many INSERT statements, or by creating a file that contains the data that you want to fill into a relation and to load the file content using the

Applied Database Systems IT 530: Project

bulk loader as we did in Homework 4. Each relation should have at least 8 records.