Reference no: EM133105985

Phase 1: Database and Entity-Relation (ER) Diagram Design

Turn in a hard copy of the ER diagram and the schema of the relational tables.

During Phase 1, your task is to design a conceptual model of the database and draw an ER diagram that can capture the information needed for this database. You need to model the data stored in the database as entity sets and relationship sets with cardinality and participation constraints, show attributes and identify primary key for each entity set. Then, you should translate your ER diagram into a set of relational tables with primary key and foreign key constraints (if any) indicated. (You do not need to write create table statements.)

Your database needs to store the following information about an internet bookstore.

There should be information about books, authors, publishers, customers, orders, shopping carts, etc. Books may be hardcover, paperback, electronic and audio. Customers may be members who will get free shipping, non-members and one-time guests. Authors can also be customers.

Your application should have the following functionalities:
1. There is a super user who can modify all contents of the database.
2. Publishers can add new books.
3. Customers can register, and login with username and password, and update their own information. Customers may become members after paying membership fee.
4. Customers may make purchases of books. Customers should be able to check their order information and purchase history.
5. Customers should be able to search by keyword.
6. Different types of books may have different delivery method.
7. Customers may give books they have purchased a rating between 1 and 5 and a comment.

Phase 2: Building a Relational Database Management System

During Phase 1, you have designed the E-R diagram and relational schema of the database. In Phase 2, you will enforce the constraints and implement the desired functionalities as specified in Phase 1's project description.

Your application should have the following functionalities:
1. There is a super user who can modify all contents of the database.
2. Publishers can add new books.
3. Customers can register, and login with username and password, and update their own information. Customers may become members after paying membership fee.
4. Customers may make purchases of books. Customers should be able to check their order information and purchase history.
5. Customers should be able to search by keyword.
6. Different types of books may have different delivery method.
7. Customers may give books they have purchased a rating between 1 and 5 and a comment.

Phase 2 of the project contains two parts. Part 1: Create and populate the database:
1. Create a database called "bookstore" for user "root". Do not specify password for root.
2. In "bookstore", create tables by running a "DB2.sql" file and populate the tables with your own fabricated data.
• Specify primary key for each table.
• Tables that are translated from relationship sets, weak entity sets, and subclasses should have foreign key constraints specified.
• Each table should contain at least 5 records. Do not insert more than 50 records for any table.
• Put all "CREATE TABLE" and "INSERT INTO" statements in one DB2.sql file so your database can be easily duplicated.

Part 2: Perform the following tasks as queries (some queries may include several steps):
1. A new customer registers, upgrades from non-member to member.
2. A publisher adds a new book with author information to the database, updates price of a book.
3. The admin (super user) updates the cost of shipping methods for books.
4. A customer searches for a particular book by title and/or author and purchases the book.
5. A guest searches for the best-selling book of a given year, if no year is given, return the best-selling book for the entire history.
6. A customer checks their order history and reorder a book
7. An author purchases their own books.
8. A customer gives rating and comment to a book they have purchased, checks rating and comments of a book.

You should have one .html file as user-interface that takes user input. You may have one short .php file for every query or one long .php file for all the queries combined.

Attachment:- Database Management System.rar