Database Systems SLP
IMPORTANT NOTE: This is the first part (SLP 1) of this assignment. Please USE this paper and the attached slides to draw an ER diagram to demonstrate relationship among the tables in the database.
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. The use of a database means that there will be an enormous capability in terms of information storage and retrieval in the store. Relational databases brings in the opportunity to cross-reference in the various tables and the performance of complex calculations on the interconnected databases for example in the store, calculations may be performed between the sales of the CDs and book table and the table that has information regarding the current inventory status of both the books and the CDs in the store (Tojo, S.2017).
Each of the tables in the database of the book store will contain both the rows and the columns, each of the rows represents a specific item that is stored in the database, and on the other hand, the columns contain the attributes that stores the characteristics of the item in the table. The attribute is therefore the name of the given column and what is featured in the fields in the database.
Employee Table
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 the employee number and the personnel information for the easy management of employee in the book store (Coronel, C.2018). 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 figure below:
Column | 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 |
Product Information Table
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 the item number which will act as the primary key in the table; other columns will include the product name, the normal price, sale price and finally the product specifics. 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 in the book store and even make a comparison. this will provide a final assurance in terms of assurance on the final sales (Chang, C.2012)
Sales of CDs and Books Table
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 the Sales ID which will represent the primary key in the table and will be used in the identification of the order, the 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, InvoiceAccount attribute will represent the customer account to be invoiced as a result of the sale, DeliveryDate attribute represents the date that the shipment or the delivery of the product sale will be made to the customer, SalesTaker attribute in the system will represent the employee that received the order on the specified book or CD.
Inventory Table
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:
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. |
In conclusion all the tables for both the employees the product information, inventory and the sales will finally be integrated to create an easily manageable relational database that will represent the information of the individual items and can be used by both the management and the employees of the book store in running the day to day activities. Some of the tables will be configured to represent the foreign keys and hence can be mapped on a separate table within the same database (Liu, H.2012).
Reference
Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems. 1272 Seiten: s.n..
Nguyen, N. T., Tojo, S., Nguyen, L. M., & Trawiński, B. (2017). Intelligent Information and Database Systems: 9th Asian Conference, ACIIDS 2017, Kanazawa, Japan, April 3–5, 2017, Proceedings, Part II.
Coronel, C., & Morris, S. (2018). Database systems: Design, implementation, and management.
Chang, C. J., & Ingraham, L. R. (2012). Modeling and designing accounting systems: Using Access to build a database. Hoboken, N.J: Wiley.
Liu, H. H. (2012). Oracle database performance and scalability: A quantitative approach. Hoboken, N.J: Wiley.
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper
Get Answer Over WhatsApp Order Paper Now