Relational Database Design and SQL Using Microsoft Access, develop a database th
ID: 3839095 • Letter: R
Question
Relational Database Design and SQL
Using Microsoft Access, develop a database that is in 3NF.
A Microsoft Access database has been created to store purchase orders by someone unfamiliar with relational database concepts, and it is therefore not normalized. Several new entities and relationships will be required to create the normalized database. Make improvements in the way attributes names have been assigned, and create the relationships necessary to produce a normalized database.
The business rules for the database are as follows:
- Each invoice can have only one department.
- A department can have many invoices.
- Each invoice can have only one job.
- A job can have many invoices.
- Each invoice can have only one vendor.
- A vendor can have many invoices.
- Each invoice can have only one employee.
- An employee can have many invoices.
- Each invoice can have many detail lines.
- An invoice detail lines belongs to only one invoice.
- Each invoice detail lines has only one part.
- A part can have many invoice detail lines.
Below is an example of an invoice which is produced using the data stored in the database.
Take a snapshot of the MS Access answer and post it as an answer
P. O. 95005 PURCHASE ORDER 1200 Machine Shop VENDOR: ATL001 DEPT: ATL Tools Ltd. BY: 1757 George Jones 1234 Main Street Vancouver, BC JOB J15766 Small Tools V5R 6J8 CONTACT: John Smith 604-123-4567 PART DESCRIPTION QTY PRICE AMOUNT LINE 45.00 45.00 ABC-5998 Wrench 18.00 XHN-6683 Hammer 54.00 90.00 20 4.50 BBR-5531 Socket 116.00 58.00 ESA-6028 Saw TOTAL 305.00Explanation / Answer
In the social model, a table can't contain copy columns, since that would make ambiguities in recovery. To guarantee uniqueness, each table ought to have a section (or an arrangement of segments), called essential key, that particularly recognizes each records of the table. For instance, an exceptional number customerID can be utilized as the essential key for the Customers table; productCode for Products table; isbn for Books table. An essential key is known as a straightforward key in the event that it is a solitary segment; it is known as a composite key on the off chance that it is comprised of a few segments.
Most RDBMSs construct a file on the essential key to encourage quick pursuit and recovery.
The essential key is additionally used to reference different tables (to be explained later).
You need to choose which column(s) is to be utilized for essential key. The choice may not be straight forward but rather the essential key should have these properties:
The estimations of essential key should be remarkable (i.e., no copy esteem). For instance, customerName may not be proper to be utilized as the essential key for the Customers table, as there could be two clients with a similar name.
The essential key should dependably have an esteem. At the end of the day, it might not contain NULL.
Consider the followings in pick the essential key:
The essential key might be basic and well-known, e.g., employeeID for representatives table and isbn for books table.
The estimation of the essential key ought not change. Essential key is utilized to reference different tables. On the off chance that you change its esteem, you need to change every one of its references; generally, the references will be lost. For instance, phoneNumber may not be fitting to be utilized as essential key for table Customers, since it may change.
Essential key frequently utilizes whole number (or number) sort. However, it could likewise be different sorts, for example, writings. Be that as it may, it is best to utilize numeric section as essential key for productivity.
Essential key could take a self-assertive number. Most RDBMSs bolster supposed auto-augmentation (or AutoNumber sort) for number essential key, where (current greatest esteem + 1) is doled out to the new record. This discretionary number is reality less, as it contains no accurate data. Dissimilar to verifiable data, for example, telephone number, certainty less number is perfect for essential key, as it doesn't change.
Essential key is normally a solitary section (e.g., customerID or productCode). Be that as it may, it could likewise make up of a few sections. You ought to use as couple of segments as could reasonably be expected.
How about we outline with an illustration: a table clients contains segments lastName, firstName, phoneNumber, address, city, state, zipCode. The possibility for essential key are name=(lastName, firstName), phoneNumber, Address1=(address, city, state), Address1=(address, zipCode). Name may not be remarkable. Telephone number and address may change. Consequently, it is ideal to make a reality less auto-increase number, say customerID, as the essential key.