Database development ( MIS 533 )

1. Database development(MIS 533) MBS in Management Information Systems and Managerial Accounting Systems (2007 / 2008) Fergal Carton Business Information Systems

2. Last week • Recap on CdI sales order ERD • Multiple contacts for each customer • Vans and drivers • Databases are more efficient and easier to maintain when normalised • Manufacturing concepts and planning models • WIP • BOM (= recipe) • MRP • Build to plan model • Safety stock • Visibility of inventory in WIP, Hans example • Sample data for assignment • customers from Northwind • products from CdF MIS533 / Database development

3. This week • BOM examples • Entity rules • ERD exercise • Cardinality • Northwind sales order MIS533 / Database development

4. BOM = Recipe or formula • How to produce our products • List of components including possible substitutes • How much of each • Special conditions of operation • Expected yields and labour productivity (i.e. standards) • Extrapolate a cost per unit • Stored in a Bill of Material (BOM) MIS533 / Database development

5. Sample BOM MIS533 / Database development

6. Narrative analysis rules • Look for who is doing something = Function • Mention of a department, person, responsibility • Eg. Once shipped by the shipping department • Look for action of what is being done = Process • Use of verbs (becomes, generates, requests, transfers, …) • Eg. Sales rep activities result in customer leads • Look for nouns (not people / geography) = Data • Leads, quotes orders, … • Eg. the order generates an invoice MIS533 / Database development

7. Name of Entity What is an entity? An entity is a class of persons, places, objects, events, or concepts about which we need to capture and store data. • Persons: customer, department, employee, student, supplier, … • Places: sales region, building, room, branch office, campus, … • Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle, … • Events: cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip. • Concepts: account, block of time, bond, course, fund, qualification, stock. MIS533 / Database development

8. Entity relationship diagrams • EntityAn entity is an object or concept about which you want to store information. • AttributesAttributes are the properties or characteristics of an entity. • RelationshipsRelationships illustrate how two entities share information in the database structure. MIS533 / Database development

9. ERD Exercise IreTicket.ie is a newly formed website that sells tickets to concert events in Ireland. You have been hired to design and develop the back end database to support the site. The following information has been provided to you regarding the site and how it will operate: An Internet user may go to IreTicket.ie and browse the different concerts that are scheduled in different locations around the country. They can also search for a particular concert (date, concert title etc.) by act (type, act name etc.) or by location (venue, town, county etc.). If a user decides to purchase tickets they must register on the site (username, password, first name, surname, postal address etc.). The registered customer can search for and select the desired concert, seating location and specify the quantity. Once the user submits the information they are asked to specify the payment method (IreTicket.ie takes credit cards and Paypal payments). If the customer selects Paypal they log on to that site and make the secure payment. If they choose credit card, they enter the credit card type, number, expiration date and security code. The transaction is completed once the customer specifies the delivery address for the tickets and today’s date is attached to the transaction. MIS533 / Database development

10. ERD Exerciselocate entities An Internet user may go to IreTicket.ie and browse the different concerts that are scheduled in different locations around the country. They can also search for a particular concert (date, concert title etc.) by act (type, act name etc.) or by location (venue, town, county etc.). If a user decides to purchase tickets they must register on the site (username, password, first name, surname, postal address etc.). The registered customer can search for and select the desired concert, seating location and specify the quantity. Once the user submits the information they are asked to specify the payment method (IreTicket.ie takes credit cards and Paypal payments). If the customer selects Paypal they log on to that site and make the secure payment. If they choose credit card, they enter the credit card type, number, expiration date and security code. The transaction is completed once the customer specifies the delivery address for the tickets and today’s date is attached to the transaction. MIS533 / Database development

11. ERD Exerciseidentify relationships concert act location registered customer payment method transaction MIS533 / Database development

12. Identify Relationships MIS533 / Database development How can I find out when the band I like has a concert? Where are U2 playing their concert? I am in Cork. What concerts are scheduled for my location? I am a customer and I want to purchase tickets for Live At The Marquee – Kanye West. A customer says they paid for a concert ticket but were never mailed with the ticket. How can I check that they paid? How many seats are left for the Joe Dolan concert? Once a customer buys tickets how do I know where to send them?

13. ERD Exercise: identify relationships concert act location transaction registered customer payment method MIS533 / Database development

14. Cardinality of Relationships MIS533 / Database development • For example: • Customer relates to Transaction. • What is the minimum number of transactions allowed and the maximum number of transactions allowed? • “well if I am registered as a customer I must have at least one transaction” OR • “I registered as a customer but never bothered buying tickets” • Minimum number is either 1 or 0 (depends on business model – ask your client/end-users) • “I want my customers to be able to buy more than one set of tickets” • Maximum number is “Many” • Transaction relates to Customer (because relationships are bi-directional) • What is minimum number of customers attached to a single transaction and what is the maximum number of customers allowed? • “a transaction is created by a customer. A group of customers purchasing one set of tickets is too complicated for our business. We want to keep purchasing as simple as possible” • “well a transaction (ticket sale) has to have at least one customer or there is no transaction!” • Minimum number allowed: 1 • Maximum number allowed: 1 transaction registered customer

15. Cardinality MIS533 / Database development Exercise – now work out the cardinality for all of the other relationships identified and complete the ERD. Not all information is contained within the narrative so you will need to make assumptions (educated guesses) regarding some relationships.

16. ERD Exerciseidentify relationships act act location concert registered customer transaction payment method MIS533 / Database development