Abstract
The purpose of this exercise is to use Microsoft Access NOTE to build a relational database that stores and tracks data effectively.
The case is the following: "Solomon Enterprises is a small concrete construction company. They employ truck drivers who receive a base salary as well as a commission on sales. Currently, they keep data on customers and sales orders in Excel Workbooks (see the attached file). As their company and the number of sales grows, they are finding it harder and harder to keep track of their data. They would like to create regular reports to help them with decision making. Right now, they have difficulty retrieving information quickly and easily. They need your expert advice to help them with their data storage and tracking issues."
Appendices to be obtained from customer service, who will share them upon request.
Extract
[...] Attributes: Each entity has its own characteristics (e.g., a customer has a name, an address, a phone number). Create a standardized database structure: Identify relationships between tables (e.g., an order is linked to a customer and a driver). Avoid data redundancy by structuring the information into several well-defined tables. Import Excel data into Access rather than entering it manually to avoid errors and save time. Establish relationships between tables in Access: A central transactional table could be "Sales." It would be linked to the Customers, Drivers, Products, etc. [...]
[...] Building a Relational Database for Efficiency - Solomon Enterprises CMIS351 - Assignment Database Design for Solomon Enterprises Background "Solomon Enterprises is a small concrete construction company. They employ truck drivers who receive a base salary as well as a commission on sales. Currently, they keep data on customers and sales orders in Excel Workbooks (see the attached file). As their company and the number of sales grows, they are finding it harder and harder to keep track of their data. [...]
[...] Our methodology for achieving this: To identify entities: - We isolate groups of attributes that are repeated in the file. - We identify columns that describe the same real-world object: a customer, an employee, an order, etc. - We consider the logical dependencies between these elements. Identified entities (candidate tables): Customer Indeed, columns such as Customer Name, Phone Number, and Contact Person describe a unique customer, but this information is repeated for each order they place. Therefore, these are independent data points that must be isolated. [...]
[...] However, in a database, each transaction (order) should be uniquely identified. This poses a problem when defining 2FN. We can therefore say that the Excel file does indeed contain the data needed to manage orders, but its current structure: - Is not adapted to growth (high redundancy) - It makes updates difficult and risky - It does not allow for advanced reporting or automation - It creates confusion between entities: customers, employees, trucks, orders 2. Identifying Entities (Tables) Here are the entities (future relational tables) that can be identified: Entity Main Attributes Customer Customer ID, Last Name, Phone, Primary Contact Address Address ID, Street, City, Postal Code, etc. [...]
[...] - Employee last name (this variable only has four categories: Evarez, Johnson, Robertson, Smithson). - Employee first name (like the previous variable, there are also four categories: Allison, Antonio, Emilio, John). - Date of hire (whose categories cover four years: and 1999). This data is organized into 149 rows with the primary key "order date." Using the Access Table Analyzer Wizard, it appears that my data sheet stores the same information multiple times. This is because duplicates exist, which can cause errors. [...]
