1. týden

1.  Write ERDish sentences for each relationship documented in the DJs on Demand ERD (ERD_DJ_on_demand.pdf).

 

Example:

Each CLIENT may be the owner of one or more EVENTs. Each EVENT must be owned by one CLIENT

 

2.  Given the following scenario, choose the entities and attributes. Draw the soft boxes with the entities and list the attributes underneath. Mark UNIQUE attributes with a hash mark (#), and try to determine optionality of each attribute.


Moonlight Coffees is a fast growing chain of high quality coffee shops with currently over 500 shops in 12 countries of the world. Shops are located at first-class locations, such as major shopping, entertainment and business areas, airports, railway stations, and museums. Moonlight Coffees has some 9,000 employees.

All shops serve coffees, teas, soft drinks, and various kinds of pastries. Most shops sell nonfoods, like postcards and sometimes even theater tickets. Shop management reports sales figures on a daily basis to Headquarters, in local currency. Moonlight uses an internal exchange rates list that is changed monthly. Since January 1, 1999, the European Community countries must report in Euros.

 

3. Copy and paste the Summit Sporting Goods scenario (below) into a text document and underline all nouns.

I’m a manager of a sporting-goods wholesale company that operates worldwide to fill orders from retail sporting-goods stores. The stores are our customers (some of our people prefer to call them our clients). Right now we have 15 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 and Womansports in Seattle. For each customer, we must track an ID and a 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 five regions: North America, South America, Africa/Middle East, Asia, and Europe. That’s all we track; just the ID and 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 we can put this application 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 fax the shipping information automatically through our shipping system. No, I don’t manage that area. My department just ensures that 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 that 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 customer IDs. My department is allowed to update the customer information only when an order is placed and the 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 because 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.

 


2. týden

1. Do the following "Try it/Solve it" exercises:

·        Database Design - Section 5, Practice Guide Relationship Transferability, Try It/Solve It Exercise 1 (b, d, f, h, j, l, n)

·        Database Design - Section 5, Practice Guide Resolving Many to Many Relationships, Try It/ Solve It Exercise 1

·        Database Design - Section 6, Practice Guide Normalization and First Normal Form, Try It/Solve It Exercise 3 (a, b, c)

·        Database Design - Section 6, Practice Guide Second Normal Form, Try It/Solve It Exercise 2

·        Database Design - Section 7, Practice Guide Arcs, Try It/Solve It Exercise 1

·        Database Design - Section 7, Practice Guide Modeling Historical Data, Try It/Solve It Exercise 3

2. Develop a hierarchical model and a recursive model for the following company scenario. Draw the ERD for each.

Our company sells products throughout the World. So we’ve divided our company into four major sales regions: Region 1, Region 2, Region 3, and Region 4. Each sales region has a unique region code. Each sales region is then divided into sales districts. For example, Region 1 is divided into the U.S, Canadian, and Southern districts. Each district has a unique district code. Each district is made up of sales territories. The Southern District is composed of three territories: Mexico, South America, and U.S. Territories. The U.S. District is made up of three territories: the West, Middle, and East. The Canadian District is composed of two territories East and West. Each territory has a unique territory code.

Each sales territory is then broken down into sales areas. For example, South America is made up of two sales areas: Brazil, and the Coastal sales areas. Each sales area has a unique sales area code. The Brazil area includes Uruguay, Paraguay, and Ecuador.

Each salesperson is responsible for one or more sales areas, and has a specific sales quota. We also have sales managers who are responsible for one or more sales districts and sales directors who are responsible for one or more sales regions. Each sales manager is responsible for the territories with his districts. We don’t overlap our employees’ responsibilities. Sales area is always the responsibility of a single salesperson, and our managers and director’s responsibilities don’t overlap. Sometimes our salespersons, manager, and directors will be on leave or special assignments and will not have sales turf responsibilities. We identify all our sales personnel by their employee ids.


3.–4. týden

Complete the following Exercises:

·        Database Design - Section 15, Practice Guide Anatomy of a SQL Statement, Try It/Solve It Exercises 2 and 3

·        Database Design - Section 16, Practice Guide Working With Columns, Characters, and Rows, Try It/Solve It Exercises 1, 3, 5, and 7

·        Database Design - Section 16, Practice Guide Limit Rows Selected, Try It/Solve It Exercises 1, 2, 5, 8, and 11

·        Database Design - Section 16, Practice Guide Comparison Operators, Try It/Solve It Exercises 1, 3, 6, and 8

·        Database Design - Section 17, Practice Guide Logical Comparisons and Precedence Rules, Try It/Solve It Exercises 2, 4, 5, and 7

·        Database Design - Section 17, Practice Guide Sorting Rows, Try It/Solve It Exercises 1, 3, and 4

 

5. týden

·        Database Programming - Section 1, Practice Guide Case and Character Manipulation, Try It/Solve It Exercises 1, 4, 6, 11

·        Database Programming - Section 1, Practice Guide Number Functions, Try It/Solve It Exercises 1, 2, 5

·        Database Programming - Section 1, Practice Guide Date Functions, Try It/Solve It Exercises 2, 4, 6, 8

·        Database Programming - Section 2, Practice Guide Conversion Functions, Try It/Solve It Exercises 1, 3, 6, 9

·        Database Programming - Section 2, Practice Guide NULL Functions, Try It/Solve It Exercises 1, 3, 8

·        Database Programming - Section 2, Practice Guide Conditional Expressions, Try It/Solve It Exercises 1, 2, 3

·        Database Programming - Section 3, Practice Guide Cross Joins and Natural Joins, Try It / Solve It 1, 3, 5

·        Database Programming - Section 3, Practice Guide Join Clauses, Try It / Solve It 1, 4, 7, 9

·        Database Programming - Section 3, Practice Guide Inner Versus Outer Joins, Try It / Solve It

·        Database Programming - Section 4, Practice Guide Group Functions, Try It / Solve It 2, 3

·        Database Programming - Section 4, Practice Guide COUNT, DISTINCT, NVL, Try It / Solve It 1, 2

 

6. týden

·        Database Programming - Section 5, Practice Guide GROUP BY and HAVING Clauses, Try It / Solve It 2, 6

·        Database Programming - Section 5, Practice Guide Using SET Operators, Try It / Solve It 2

·        Database Programming - Section 6, Practice Guide Fundamentals of Subqueries, Try It / Solve It 3, 4

·        Database Programming - Section 6, Practice Guide Single Row Subqueries, Try It / Solve It 4

·        Database Programming - Section 6, Practice Guide Multiple-row Subqueries, Try It / Solve It 3, 4

·        Database Programming - Section 6, Practice Guide Correlated Subqueries, Try It / Solve It 4

·        Database Programming - Section 7, Practice Guide Insert Statements, Try It / Solve It 2, 3

·        Database Programming - Section 7, Practice Updating Column Values and Deleting Rows, Try It / Solve It 1, 4, 5

·        Database Programming - Section 7, Practice Guide DEFAULT Values, MERGE, and Multi-Table Inserts, Try It / Solve It 1

·        Database Programming - Section 8, Practice Guide Creating Tables, Try It / Solve It 1, 2, 3

·        Database Programming - Section 8, Practice Guide Modifying a Table, Try It / Solve It 3, 4

 

7.–8. týden

·        Database Programming - Section 11, Practice Guide DML Operations and Views, Try It / Solve It 2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

@ Ing. Pavel Turèínek, Ph.D.