Assignment 4:  (FALL 2023 – F23)

Database Design and Microsoft Access

Due Date: Thursday, October 26 by 11:55 pm.

note : date has been changed from original Course Outline.

Late Policy:

• It is expected that students budget enough time to properly submit their assignments via OWL

and allow for any unforeseen technological issues. Students are expected to regularly backup their assignments and submit well before any deadline.

• Each student will be given four “late coupons” in total for the semester that may be used to submit an assignment one day late per coupon (up to a maximum of 4 days late per assignment).

• Each coupon is valid for a 24 period (11: 55 pm to 11:55 pm).

• Late coupons will be applied automatically when an assignment is submitted late and tracked in the OWL gradebook.

• If a student has no late coupons remaining or submits an assignment more than 4 days late, a zero grade will be given for the assignment (no partial late marks will be given).

• Late coupons must be used before special circumstances are considered.

• After all late coupons are used, no extensions will be given for assignments except for in the event of serious medical or compassionate grounds. A student must follow the procedure for   Academic Accommodation for Medical Illness as given in this document.

The Entity-Relationship Diagram is the model or ‘blueprint’ that is used to create a database.

For Assignment Four, you will assume the role as the database administrator for your company. You will take the E-R diagram supplied in this assignment and use that to create a database using MS

Access.

Project 1: Create a Microsoft Access database

(Project 1) PART 1:

You realize that in order to run your company you will need to start tracking some basic information that deals with your product fulfillment.

Specifically, you will track the shipping process of your product(s).

You will create a list of all the products your company makes. You will also make a listing of all the

Shipping Companies you use. This will allow you to track what products have been shipped and what the cost of shipping is for each order that goes out.  You will also assign employees to manage

(oversee) contacts and contracts with the Shipping Companies.  Finally, you will assign a specific employee to supervise a specific product.

Specifically, A customer will order one or more PRODUCTs at a time. Each time an order is created    will mean that a SHIPPING BILL will have to be made that contains the list of PRODUCTs that are in the order that is to be shipped. The SHIPPING COMPANY will create a SHIPPING BILL. Some

EMPLOYEEs of your company will liaison (manage) the SHIPPING COMPANYs that you use. This   means you will designate certain EMPLOYEEs to deal (liaison) with specific SHIPPING COMPANYs if there are any issues with costs or deliverables, etc. Finally, you will designate a specific

EMPLOYEE to supervise a specific PRODUCT.

You are to create the MS Access database based on the supplied E-R Diagram named Assignment4_ER_BILLING.dia.

All the information that you will need is contained within the supplied Entity Relationship Diagram.

Information on each field regarding the metadata required is included in the following E-R Diagram.

For example, in the image above the requirement for the field labeled ‘LastName” is shown. This provides:

a possible field name “LastName”

the fact that the maximum for this field is 40 characters

the fact that it is not the Primary Key

the fact that this field is not Nullable

the fact that this field is not Unique

As the database administrator you will decide on which is the best data type for each of the fields in this design.

You will also decide if the provided labels are the best suited to convey the contents of each field.

This means you are expected to change any field you feel is not descriptive enough or does not fit

your ‘style’ of database design. When you change any of the labels for your database, you must also change the supplied E-R diagram with the new name.  For example, if you decided to change the

field name above from “LastName” to “Niblick”, you will have to make the same change to the E-R

diagram. In the end, the E-R diagram attributes must match your database field names that you

selected. You are not allowed to change any of the metadata (Nullable, Unique, Characteristics, etc.). Only the label name can be changed by you.

Addition Information:

a)  For the “ PRODUCT” information use the following specifications:

1) Unique PRODUCT ID that is automatically created when a new item is entered note: this ID must start with YOUR initials:

so – if your name is Dolly Madision – every supply ID would start with DM example: DM0001, where “DM” are YOUR initials

DM0002, where “DM” are YOUR initials

DM0004, where “DM” are YOUR initials

DM0007, where “DM” are YOUR initials

hint: MUST be 2 initials and four digits (see examples above) use Autonumber (Long Integer)

NOTE: - very important :

if the PRODUCT ID is used as a Foreign Key in another table:

- that key must be of type Long Integer

- the value entered will just be the number:

example:  the foreign key is 1 where the PRODUCT ID is DM0001 the foreign key is 2 where the PRODUCT ID is DM0002 the foreign key is 4 where the PRODUCT ID is DM0004 the foreign key is 7 where the PRODUCT ID is DM0007

b)  For the “SHIPPING BILL” information, use the following specifications:

1) SHIPPING BILL primary key:

Unique SHIPPING BILL IDENTIFICATION VALUE that is NOT automatically created when

a new SHIPPING BILL is entered

note: this ID must start with ANY alphabetic character followed by 4 digits: the letter MUST be a capital letter

example: K9345  or  F0302  or   X3000

note: it does not have be the same letter each time.

NOTE: - very important :if the SHIPPING BILL ID is used as a Foreign Key in another table:

- that Foreign Key in the other table must be of the same type.

It is important to ensure the SHIPPING BILL Primary Key adheres to this standard and that any Foreign Key used to associate the SHIPPING BILL table matches.

Look at the difference and use of Input Mask versus Format in the metadata.

c)  For the “SHIPPING COMPANY” information use the following specifications:

1) SHIPPING COMPANY primary key (PID):

Unique SHIPPING COMPANY IDENTIFICATION VALUE that is NOT automatically created when

a new item is entered

note: this ID is made up of numbers only, but can start with zero (0):

example: 15365254 or 00025 or 1000101

note: minimum size: 4 numbers – maximum size: 10 numbers

Look at the difference and use of Input Mask versus Format in the metadata.

d)  All Date Fields in the database:

All dates in your database must display as: year-short name of month-day. the day must always be two numbers. (i.e. 02 or 12 or 30 )

example: 1988-Nov-10 or 2001-Jul-06

BUT, the user can enter the date in any fashion and the computer will translate.

example: user enters 5 6 98 and the computer displays this as 1998-May-06 Look at the difference and use of Input Mask versus Format in the metadata.

NOTE: If you are unsure of any of the specifications above, please post your question to the Forums under Assignment Four. We welcome and look forward to any and all inquiries. This will allow for

standardization of response. Any emails regarding the specifications above will be directed to please post to the Forum.

REMEMEBER:

You MUST change the metadata based on your E-R diagram (image of Doctor’s Office example):

NOTE: The ‘Allow Zero Length’ field MUST ALWAYS be set to ‘No’ .

- ‘ Required’ is based on participation and ‘Indexed’ is based on cardinality when translating the Foreign Keys.

(Project 1) PART 2: NORMALIZATION

Normalization is the process of structuring a relational database in order to reduce data redundancy and improve data integrity.

A field in the SHIPPING BILL table must be normalized.

You are to identify that field and then conduct the Normalization process on just that field. (See the notes on how to normalize a field).

You will amend the Entity Relationship diagram supplied in this Assignment Four to accommodate the Normalization of that field.

hint: you can make the new Primary Key of any data type you wish – there is no wrong type.

You must change the specifications of the changed field in the ER Diagram and then make these changes to the actual database. Your data entry will be based on these changes.

Note: Yes, other fields in other tables should also be normalized, but this assignment only requires you to deal with the SHIPPING BILL table.

Once this is done, you will add those changes to the MS Access database you have created for this Assignment. You will submit the ER diagram with your amendments for this assignment.

(Project 1) PART 3: REFERENTIAL INTEGRITY

You must build all the relationships described in your diagram.  You must use the Relationships Database Tool in MS Access to enforce the relationships as demonstrated in class.

You must add ALL of the relationships from the amended Entity Relationship diagram. You must Enforce Referential Integrity in ALL the relationships.

example using the Doctor’s Office from the notes/videos:

Hint: The Relationship window (like the one above) that you will create for this assignment will look like the correctly drawn Entity-Relationship diagram supplied for Assignment Four.

That is why a correct ER diagram is required in order to know how to set the meta data in the database.

(Project 1) PART 4: DATA INPUT

You must fill in the tables with example data of at least four (4) records for each and every table.

Your first and last name must be one of the names in the EMPLOYEE table.

You are allowed to make the data appear meaningful and not just a random set of letters.

BUT ! Do not spend a lot of time thinking up data. Just enter anything that is even a close fit.

So, do not just enter a bunch of letters (i.e. ‘sfsdfsd’ or ‘grgrsdf’ or ‘hjksdjfs’) but actual words and numbers. We just want to see something entered in each required field.

Complete the above as required saving the database in your “BILLING” database. e.g. youraccountname_BILLING.accdb

Project 2:  Information Systems Questions about Your Company

Create an MS Word document and complete the following questions pertaining to the business you described in Assignment One (1).

Each answer must be comprehensive (more than one sentence). Each answer requires at least four   sentences. The entire Project 2 should be at least approximately 600 words.  It is expected that some thought and explanation is included in this section.

1.) Identify which of Porter’s Five Forces might influence your company the most and why.

2.) With which competitive strategy (Cost+Differentiation) does your company position itself and why?