Las Positas College



CIS 9002 – Oracle Database Design NAME: __________________ HW1aDownload this document, save this file using the filename: your_name_CIS9002_ HW1a.docx Insert your answers into this document. Submit the assignment via email as an attachment to cis_assignments@#1 Read the following scenarioDJs on DemandWe started out as a group of friends who organized parties and customized our own music. Then we thought we’d turn it into a business to pursue our interests and earn some money. ?We called ourselves the “DJs on Demand.”Everyone who works here is partner. Every partner has a specific responsibility. The project manager makes initial contact with the client to discuss the event. Is it a birthday party, a wedding, an anniversary, a graduation? ?What is the date for the party or event? Once that’s decided, the event planner gets in touch with the client about specific locations, catering, decorations, and other specific details. The DJ talks with the client about the kind of music wanted. The project manager supervises the event planners and DJs. ?He/She also authorizes expenditures related to a project.We have a large collection of CDs. Each CD contains several songs, and the same song can appear on several CDs. We like to classify each song by type (hip hop, salsa, R & B (rhythm and blues), techno, salsa, meringue, polka, rock, jazz, new age, classical, etc.) ?We can propose an initial list of songs to the client depending on the event. Of course, a client can request other songs as well.Our client list is growing. We have a lot of repeat business -- customers who like what we’ve done and ask us to work their other events. We have some very busy customers who can have more than one event going on at the same time! ?We also have a list of themes that we can use to categorize these events. For example: a wedding may have a tropical theme, a party may have a carnival theme, an anniversary could have a sixties theme, etc. This helps us pick a venue and also gives us an idea of what the DJ (and other musicians) should wear. Some partners have a specialty or expertise -- so a theme can also help us assign the right person to the job.Events are held either in a public space or a private home. The event manager visits both and makes arrangements with the public-space renter or the private-home owner.Since several partners can work on an event, and an event can be assigned to several partners, we like to keep track of who is working on which event. We keep a log of what each event planner and DJ has done on a project, and when they did it.#2 Review the Entity Relationship Diagram (ERD) for the DJ on Demand above scenario. This Entity Relationship Diagram would be created by database designer or systems analyst AFTER reviewing the above scenario. The boxes are called ‘soft boxes’, the ENTITY of each box is in UPPER case letters. The attributes of each entity is listed after the ENTITY.ANSWER the following questions:How many entities in this ERD: ________________ What is the meaning of the following symbols: # __________________________________, * ____________________________ , o __________________________________, - - - - - (dash line) __________________ , ----------- (solid line) __________________ , ___________________________#3 The goal of this practice is to recognize attributes for an entity.The three entities that play a role in a DJ business: SONG, EVENT, and CUSTOMER are listed as the first three column headings of the table below. The fourth column contains a collection of attributes. Use a X mark to indicate if the attribute listed could be an attribute for the entities listed. (For example, could Title be an attribute for Song, for Event, and for Customer?)SONG EVENT CUSTOMER XTitle DescriptionVenue First NamePhone NumberRelease dateLast NameType Email address#4 Complete the ERDish statements for the above DJs on Demand ERDUsing the above ERD, write each relationship in ERDish:For Example, the relationship between EVENT and CLIENT:470281013716000Each CLIENT may be the owner of one or more EVENTsEach EVENT must be owned by one and only one CLIENTThe relationship between EVENT and PLAY LIST ITEM:____________________________________________________________________________________________________________________________________________The relationship between EVENT and THEME:____________________________________________________________________________________________________________________________________________The relationship between PLAY LIST ITEM and SONG:____________________________________________________________________________________________________________________________________________The relationship between SONG and TYPE:____________________________________________________________________________________________________________________________________________The relationship between SONG and TRACK LISTING:____________________________________________________________________________________________________________________________________________The relationship between TRACK LISTING and CD:____________________________________________________________________________________________________________________________________________#5 Begin to identify fields in a database. Highlight in YELLOW all significant nouns. Hint: there are between 10 – 15 nouns/entities to be highlighted. Nouns will usually become Entities (records in your tables) in your ERD..SUMMIT SPORTING GOODS I’m a manager of a sporting goods wholesale company that operates worldwide to fill orders of retail sporting goods stores. The stores are our customers (some of our people prefer to call them our clients). Right now we have fifteen customers worldwide, but we’re trying to expand our customer base by about 10% each year starting this year. Our two biggest customers are Big John’s Sports Emporium in San Francisco, CA., USA, and Woman sports in Seattle, Washington, USA. For each customer we must track an ID and name. We may track an address (including the city, state, zip code, and country) and phone number. We maintain warehouses in different regions to best fill the order of our customers. For each order we must track an ID. We may track the date ordered, date shipped and payment type when the information is available. Right now we have the world divided into fife regions: North America, South America, Africa/Middle East, Asia, and Europe. We only track the ID and the name. We try to assign each customer to a region so we’ll generally know the best location from which to fill each order. Each warehouse must have an ID. We may track an address (including the city, state, zip code and country) and phone number. We currently have only one warehouse per region, but we’re hoping to have more soon." "I manage the order entry functions for our wholesale sporting goods business. My department is responsible for placing and tracking the orders when our customers call. For each department we must track the ID and name. Sometimes, our customers just mail us the orders when they are not in a rush, but most often they call us or fax us an order. We are hoping to expand our business by providing immediate turnaround of order information to our clients. Do you think this application might be able to be put on the Web? We can promise to ship by the next day as long as the goods are in stock (or inventory) at one of our warehouse locations. When the information is available, we track the amount in stock, the reorder point, maximum stock, a reason as to why we are out of stock, and the date we restocked the item. When the goods are shipped, we intend to fax the shipping information automatically through our shipping system. No, I don’t manage that area. My department just ensures our customers have the correct billing information and verifies that their account is in good credit standing. We may also record general comments about a customer." We do make sure all the items they have requested are in stock. For each item we track an ID. We may also track the item price, quantity, and quantity shipped if the information is available. If they are in stock, we want to process the order and tell our clients what the order ID is and how much their order total is. If the goods are not in stock, the customer tells us whether we should hold the order for a full shipment or process the partial order." The accounting department is responsible for maintaining the customer information, especially for assigning new customers IDs. My department is allowed to update the customer information only when an order is placed and their billing or ship-to address has changed. No, we are not responsible for collections. That’s all handled by accounts receivable, I also think that the sales reps get involved as their commission depends on customers who pay! For each sales rep, or employee, we must know the ID and last name. Occasionally we need to know the first name, user ID, start date, title, and salary. We may also track the employee’s commission percent and any comments about the individual." "Our order entry personnel are well versed in our product line. We hold frequent meetings with marketing so they can inform us of new products. This results in greater customer satisfaction because our order entry operators can answer a lot of questions. This is possible because we deal with a few select customers and maintain a specialty product line. For each product we must know the ID and name. Occasionally we must also know the description, suggested price, and unit of sale. We would also like the ability to track very long descriptions of our products and pictures of our products, when it is necessary."#5 Begin to organize those items (fields) into tables..HINT: depending on how you organize there will be between 5 to 9 tables.PRODUCT tableITEM table___________________________ 4. ______________________________________________________ 6. ___________________________7.___________________________ 8. ___________________________9.___________________________See ERD diagram next page to help you3465195236855ITEM00ITEM617855596900064770099060PRODUCT00PRODUCT ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download