Access Application Development...

Access Application Development

This database application can be used by the employees and managers of the CD
warehouse, the customers, the suppliers, the database administrator, etc. ln the following figure we present a tentative layout of the system.
ln the welcome screen, the user can choose one of the three options presented. we give details about the forms or set of forms to be included in each option; however, you are encouraged to add other forms you find relevant. we suggest that the queries, forms, and reports already created in the Access Application Development section be included in here.

Read more »

Database Design...

Database Design

We present the main entity types of this database. For each entity type, we provide some of the corresponding attributes. Use this information in order to: (a) Build an Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database. identify the primary key(s) and the foreign key(s) for each relation. Draw the relational integrality constraints; (c) For each of the. relations created, indicate its normal form. lf the relation is not in the 3NF, decompose it into 3NF relations.

1. Album. The main attributes are identification number, name, songs, members, release date, warehouse location, quantity available, category, etc.

2. Customer. The main attributes are identification number, name, address, telephone number, e-mail, credit card information, music preferences, password, balance, etc.

3. Order. The main attributes are identification number, order date, description, expected delivery date, actual delivery date, payment type, payment amount, payment date, etc.

4. Supplier. The main attributes are identification number, name, address, telephone number, current balance, etc.

Note the following: (a) Music preferences is a multi-valued attribute of the entity type Customer, (b) Whenever an album is purchased from a supplier, the following is recorded: purchase date, purchase price, guantity purchased. (c) when an album is ordered by a customer, the price is recorded. The price of an album changes with time. (d) The attribute Songs of the entity type Album is a. multi-valued attribute. (e) Members is a multi-valued attribute of the entity type Album.

Entity Relationship Diagrams

The ERD already in 3NF, because there is no partial and transitive dependencies.

Entity Relationship Diagrams - Mapping

Read more »

CD Warehouse Management...

Problem Description

The purpose of this project is to develop an interactive information system for a CD warehouse. This system will enable the company to create an online ordering system interface , between the company and its customers for everyday transactions.

The database that we will create has two kinds of end-users: customers and company employees. Customers have access to the company's website and should be able to open accounts, do searches for the album that they want to purchase, place orders, and check the current status of their account balances. While searching for an album customers may know as little as the title of a hit song from the album to the name of the bassist of the group. Therefore customers should be able to make searches by using one of the following: group name, album name, and song title. After finding the album that the customers want to buy, they can put it in their shopping cart, and at the end of the session they can place and order. At any time, the customers can check their order's status and their account balance.

The company's employees periodically check the database to identify the customers with positive balance and send the bill. They are also interested to know about customers' preferences. Then customers are informed about new releases according to their preferences. When an order is received, an employee checks the availability of the albums. If the albums are available, they are mailed to the customer right away, and the inventory level is updated. Customers are informed about the shipment date, and their balance is updated. If the albums are not available, the suppliers are contacted. The supplier with the lowest cost and closest distance to the warehouse is chosen. At the beginning of each month suppliers get a payment from the company. Products are shipped through UPS.

Read more »