Database Systems SLP
lord mots
SLP1Slides.pptxDatabase Systems
Student Name
Tables
Employee Information
Product Information
Books and CDs sales Information
Inventory
The database for the book store will contain a set of tables with different information that will be necessary for the store to store and retrieve information. The database will serve in aiding both the employees and the management in terms of retrieval and storage of information (Elmasri, R.2017). This will also make it efficient for the management to make decisions regarding the book store.
The tables to be included in the process of designing the database includes tables that will store the employee information, table to store the information on the books and the CDs, table to store information regarding sales of both the books and the CDs and finally the current inventory status of both the books and the CDs in the store.
The database will more powerful and efficient compared to the current mode of data storage in the book store which mainly uses excel and the spreadsheet.
2
Employee Information
| Data Type | Description | ||
| EmployeeId | Integer | Represents the primary key of the table employees. | |
| FirsName | Char | Represents the first name of the employee | |
| LastName | Char | The second name of the employee in the table | |
| Address | varchar | Gives the residential address of the employee | |
| Salary | Integer | Shows the amount earned by the employee | |
| Gender | char | Shows whether the employee is male or female | |
| Birthdate | datetime | Date of birth of the employee | |
| Contact | integer | Shows the phone contact or telephone of the employee | |
| Hire date | Datetime | Shows the date the employee was hired in the book store |
The first table that will be created as part of the database of the book store will be the employee table. The employee table will list the basic information of the employees by:
Employee ID number: Represents the primary key of the table employees.
Personnel information (name, address, gender birthdate and contact): for the easy management of employee in the book store.
Hire date: Shows the date the employee was hired in the book store
Salary: Shows the amount earned by the employee.
While designing the employee table of the bookstore the primary key of the table will be represented by the employee id. Hence the various columns that will feature in the employee table can be shown in the table.
3
Product Information
| Item Number | Product Name | Normal Price | Sale Price | Current Inventory | |
| Integer | Char/Var | Integer | Integer | Integer | |
| Integer | Char/Var | Integer | Integer | Integer | |
| Integer | Char/Var | Integer | Integer | Integer | |
| Integer | Char/Var | Integer | Integer | Integer | |
| Integer | Char/Var | Integer | Integer | Integer | |
| Integer | Char/Var | Integer | Integer | Integer | |
| Integer | Char/Var | Integer | Integer | Integer |
Inventory Data
The product information table in the database of the bookstore will offer a description of all the products in the databases including both the CDs and the books. It represents a description of all the products that the book store can either deal in through both buying and selling. This will help during the identification of the individual products while making inventories in the database.
Some of the key attributes that the product information table of the book store will display will include
Item number: will act as the primary key in the table.
Other columns will include
Product name: This is the actual title of the product.
Normal price: This is the price before discount.
Sale price: This is the discounted price.
Current Inventory: This is what’s currently in stock.
The product information table will provide an organized view of books and CDs that are in the database to the customers in the book store and also give them the ability to know the specification of the various books and CDs and even make comparison.
4
Sales
| Sales ID | Sales Name | Customer Account | Invoice Account | Delivery Date | Employee ID | Units Sold | |
| 1 | 393432 | 42 | 2 (credit card) | 1/10/15 | 4269 | 3 | |
| 2 | 234321 | 40 | 2 | 1/11/15 | 8934 | 2 | |
| 3 | 557842 | 50 | 2 | 1/12/15 | 5519 | 2 | |
| 4 | 337891 | 46 | 1 (check) | 1/14/15 | 6598 | 3 |
The sales table in the database of the book store will enable both the management and the employees of the book store to closely monitor the sales regardless of whether the sales have been posted or not. The various columns that will feature in the sales will include:
Sales ID: which will represent the primary key in the table and will be used in the identification of the order.
Sales Name: attribute will provide a description of the order that was made that resulted to the sale.
Customer Account: will store the information regarding the customers that made the order the at resulted in the given sale.
Invoice Account: attribute will represent the customer account to be invoiced as a result of the sale.
Delivery Date: attribute represents the date that the shipment or the delivery of the sale will be made to the customer.
Employee ID: attribute in the system will represent the employee that received the order on the specified book or CD.
Units Sold: how many products sold.
5
Inventory
| Name | Type | Description |
| ItemId | Int | Represents the primary key in the inventory table. |
| EntryDate | datetime | It represents the day the given item was entered to the inventory table in the database. |
| TrackInventory | Char | Controls on whether or not the given book or CD should be tracked according to the receipt table or not. |
| LastUpdate | TimeDate | Controls in the inventory table the most recent time the given row of book or CD was last updated to represent the most recent information on the item. |
| Quantity | Int | Represents the quantity that was altered while making the given inventory. |
The Inventory table in the database of the book store will store the inventory information based on the individual product id of both the books and the CDs. The attributes that will feature in the table will be as shown in the below table.
Item ID: Represents the primary key in the inventory table.
Entry Date: It represents the day the given item was entered to the inventory table in the database.
Track Inventory: Controls on whether or not the given book or CD should be tracked according to the receipt table or not.
Last Update: Controls in the inventory table the most recent time the given row of book or CD was last updated to represent the most recent information on the item.
Quantity: Represents the quantity that was altered while making the given inventory.
6
