MIS 3376, Spring 2018 (Grimes)

Assignment 1

Assignment 1: Who uses databases?

In this assignment, you will identify three companies and think critically about how they use databases to enable their business. After completing this assignment, you should be able to identify the types of data a company might be interested in storing, and how it is related to other data. You should also begin to see complexities in identifying how data is stored.

1. Choose three of the categories below and identify one specific company, organization, or service from each.

	Banking/Finance	Retail	Education	Entertainment
Examples	Banks Investment firms Insurance	Physical stores Online stores Convenience stores	Grade schools Colleges Training seminars Online learning	TV, music, and movies Streaming services Television networks Gaming
	Food Services	Healthcare	Service	Transportation
Examples	Restaurants Groceries Farming Food production	Doctors, dentists, etc. Hospitals/urgent care Pharmacies	Government svcs. Postal service Construction Lawn care	Auto manufacturing Taxi services Airlines Shipping

2. For each company, identify two entity types they need to keep track of.

3. For each type of data, identify five attributes for each entity type you identified in #2

4. Create a data dictionary showing the column names, table names, data type, and length for the entities in #2. See page 15 in your book for an example.

5. Draw an ERD showing the relationship between the two entities. Include the cardinality and participation constraints. You do not need to show the attributes in the ERD. It is easiest to make these drawings in software such as PowerPoint or Visio.

6. For each entity, provide 3-7 instances using fictional data. Create and populate a table for each entity that includes sample values that demonstrate the participation and cardinality.

7. Write out the business rules that are presented in your ERD. The business rules should describe the entities, their attributes, domain constraints (optional, but should be included if needed), cardinality, and participation constraints. You may either write this in long form or use bullet points.

Name your file Lastname_Firstname_A1.docx (i.e., Grimes_Mark_A1.docx) and upload to Blackboard by 10:00 PM CST on Monday, February 5

Notes

1. Do not pick multiple companies from one category, and do not use the same company for multiple categories.

2. Do not use the same (or very similar) entities for every company. For example, do not just create a relationship of “Customers purchase products” for every company. Not cool and won’t count.

3. Use attributes that companies would realistically have access to. For example, most retailers would not know (or need to know) a customer’s weight.

4. Some companies fit in to multiple categories – i.e., Walgreens could be considered both “retail” and “healthcare”. Choose the category that matches the aspect you are describing.

5. You may choose a “service” rather than a company – for example, Amazon Prime Video is a service owned by Amazon, but would be an acceptable service.

6. I anticipate each company will take approximately 1.5-2 pages – so your final deliverable should be around 5-6 pages.

7. If you have read ahead or have database experience and want to demonstrate your advanced knowledge that is fine – however, artificial keys and foreign keys do not count as attributes for this exercise.

8. Please put some thought into the companies you select and do not just go with obvious/easy choices. For example, “Amazon” is likely the first e-commerce company you thought of – and I don’t want everyone to pick Amazon. This is both very boring to grade and does not encourage you to think critically. You don’t need to pick a company that is super obscure, but try to pick something interesting. There are plenty of well-known companies to go around.

Assignment 1 Example

1. Company #1: Mark’s Horse Hostel (Service)

2. Entity types: Horses, Customers

3. Attributes: Horses (Name, Weight, Gender, Color, Owner Name) Customers (Name, Phone, Address, Email, Balance)

4. Data Dictionary

Column	Table	Data Type	Length
Name	Horses	Text	30
Weight	Horses	Number	16
Gender	Horses	Text	1
Color	Horses	Text	10
Owner Name	Horses	Text	30
Name	Customers	Text	30
Phone	Customers	Text	12
Address	Customers	Text	50
Email	Customers	Text	50
Balance	Customers	Number	16

5. ERD

6. Tables

Horses
Name	Weight	Gender	Color	Owner Name
Amy	700	F	Brown	Norman Johnson
Dave	1200	M	Brown	Mark Grimes
Ed	1000	M	Black	Richard Scamell
Sarah	1500	F	Red	Mark Grimes
Tom	1100	M	Black	Richard Scamell

Customers
Name	Phone	Address	Email	Balance
Mark Grimes	281-777-6666	40 Wallaby Way	gmgrimes@uh.edu	2000
Norman Johnson	281-666-7777	41 Wallaby Way		3000
Richard Scamell	281-555-9999	42 Wallaby Way	rscamell@uh.edu	2000

7. Business rules

Mark’s Horse Hostel is a business that cares for horses owned by customers. All customers must own at least one horse, but some customers have many horses. All horses kept at Mark’s Horse Hostel are owned by exactly one customer. Due to size limitations of the stables, all horses must weigh less than 2,000 pounds. A horse’s color may be specified as Black, White, Dark Brown, Light Brown, Red, Yellow, or Other. Customers are not required to provide their email address.

Or you can use bullets:

· All customers have at least one horse

· A customer may have multiple horses

· A horse is owned by exactly one customer

· All horses must weigh less than 2,000 pounds

· Horse color is classified as Black, White, Dark Brown, Light Brown, Red, Yellow, or Other

· Customer’s email address is optional

Notes about the assignment:

You may noticed that I have not included attributes such as “CustomerID” or “HorseID” that would make for good (artificial) primary keys. If you want to include something like that it is fine, but do not count that as one of your five attributes – list things that are meaningful to describing the entity instances. The point of this assignment is to critically think about what types of data companies need to store to do their job. We will deal with key values and their relationships more in future assignments.

For the length of text attributes, put what you think a reasonable number of characters would be. For example, for gender we can just store “M” or “F”. For Number values, the length is the number of bits – so 16 means 2^16 = 65,536 (any number between 0 and 65,535 will fit). You can just use 16 for the length of numbers, unless you want to be more precise in your answer.

I provided some domain constraints in the business rules – I would encourage you to do the same where it is appropriate (You don’t need a constraint for every attribute, but if there are places a domain constraint would be beneficial you should mention it).

You can make the ERD directly in word (using the shapes on the “Insert” tab), or make it in PowerPoint, Visio, etc. using the drawing tools. There are several ways to save your drawings as an image you can insert into the word document, one easy way is to take a screenshot (Google or ask a friend if you need help with this).