Create Each Table (Shown In Figure 1) In MS Access Database
Create each table (shown in figure 1) in a MS ACCESS database
Create each table in a MS ACCESS database. Define the primary key of each table. Select appropriate data types for each column. You should also define the necessary constraints (such as, range, default value) for the columns. Please define the necessary relationships between the tables. Insert the data (shown in figure 1) into the tables. Submit the database through Blackboard.
Paper For Above instruction
Create each table (shown in figure 1) in a MS ACCESS database
Premiere Products, a distributor of appliances, house wares, and sporting goods, required an effective database system to manage its extensive data. Transitioning from spreadsheets to a relational database management system like MS Access was essential to eliminate redundancy and improve data accessibility. The primary task involved designing seven tables reflecting the company's data entities: CUSTOMER, ORDERS, SALES_REP, PART, ORDERLINE, SUPPLIER, and PART_SUPPLIER. Each table's structure, including defining primary keys, selecting appropriate data types, and setting constraints, was crucial to ensure data integrity and facilitate effective data management.
Design and Creation of the Tables
Creating the tables in MS Access involved careful planning of primary keys and relationships among entities. The CUSTOMER table included fields such as CustomerNum (primary key), CustomerName, Street, City, State, Zip, Balance, CreditLimit, and RepNum. Data types like Text for names and addresses, Number for numeric values, and Currency for financial figures were chosen. Constraints such as setting the CustomerNum as auto-incremented primary key, positive ranges for balances, and default values where necessary helped maintain data quality.
The ORDERS table captured order information, with OrderNum as the primary key, OrderDate as Date/Time, and foreign keys linking to Customer and Sales_Rep tables. Similarly, the SALES_REP table included RepNum as primary key, with attributes like LastName, FirstName, Address, TotalCommission, and CommissionRate, with suitable data types and validation rules.
The PART table contained PartNum (primary key), Description, OnHand, Class, Warehouse, and Price. The Part_Supplier junction table managed many-to-many relationships between parts and suppliers, with composite keys of PartNum and SupNo. The SUPPLIER table recorded supplier details, including SupNo (primary key), SupName, Address, City, Contact_Name, and Phone. Foreign keys and referential integrity constraints ensured consistency across the database.
Establishment of Relationships
In MS Access, relationships were defined to enforce data integrity and model real-world connections. For instance, a one-to-many relationship from SALES_REP to CUSTOMER indicated that each customer was assigned to a single sales representative, but a sales rep could serve many customers. Orders were linked to customers via CustomerNum, and order lines linked to orders through OrderNum. The many-to-many relationship between parts and suppliers was resolved by the PART_SUPPLIER junction table, with foreign keys referencing PART and SUPPLIER tables.
Data Entry and Population
Data from the provided figures was entered into the respective tables: Customer, Order, Sales_Rep, Part, OrderLine, Supplier, and Part_Supplier. Additional fake entries for customers, orders, suppliers, and parts were created to meet the assignment requirements, ensuring all data were consistent with the specified constraints—for example, all new customers being located in Florida, and orders dated within 2014.
Conclusion
Constructing this database involved meticulous planning of table structures, relationships, and data constraints. Using MS Access's tools for referential integrity, data validation, and form creation, the resulting database provides a scalable and reliable solution for Premiere Products’ data management needs. This transition from spreadsheets to a relational database facilitates efficient data retrieval, reduces redundancy, and supports the company's growth in managing vital business information.
References
- Hernández, M. J. (2018). Database Design for Mere Mortal. Morgan Kaufmann.
- The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. Wiley.