W3030 What size is this jeans warehouse

BBS Gerolstein

Computer science

Databases exercises

www.p-merkelbach.de

-1-

© Merkelbach

Databases

Exercise 1 - Jeans Storage

1. Open the database management program and create a new (empty) database to manage a jeans warehouse. 2. Save this database under the name Jeans 3. Create the following new table in the design view: 4. Save the table under the name Jeans. 5. Place a primary key on the JeansNr field. 6. Create a simple form (in blocks, color: transitions) with the Jeans table. 7. Save this form under the name Jeans_form 8. Enter the records from page 2 either in the form or in the table. 9. Sort the records in ascending order by price. 10. In the Jeans table, replace the color bleached with light blue. 11. Change the column width of the Size field to 8, assign the optimal width to the Number column. 12. Create a query that contains the following data: Name and Color 13. Save this query with the name Jeans Color. 14. Create another query in which all jeans with a price below 50 are displayed. The data should be displayed in ascending order according to the price. 15. Save this query under the name Offers. 16. Create a report on the Jeans table that includes all fields and records (landscape format). Use as store name: Jeans stock. a) Assign the Comic Sans MS font, font size 28 pt. to the Jeans heading. b) The list (detail area) should be written in Arial, 12 pt.; c) Add the heading Inventory in the report. 17. Close the program. Field name Field data type Field size, format JeansNr Name Size Color Price Quantity In stock Auto value Text Number Text Currency Number Yes / no Euro integer integer

www.p-merkelbach.de

-2-

© Merkelbach

Databases

Exercise 2 - Used Cars

1. Create a new database called Auto Sales. 2. Create a new table with the following fields and field properties in the design view: Field name Number Brand sold Field data type Auto value Text Date Field size, format 25 short

3. Place the primary key in the NUMBER field and save the table under the name Inventory. 4. Add the following data records to the Inventory table: Number (car value) (car value) (car value) (car value) (car value) Brand Audi 80 Mazda VW Golf Fiat Marea Ford Escort sold 9/12/2003 6/24/2002 10/16/2003 3.1. 2004 May 12, 2001

5. Add a new column with the field name Color after the Brand column and assign any color to each data record. 6. Move the Color column to the last position in your table. 7. Close the database program.

8. Open the database used cars.odb and open the table used cars there and sort the table in ascending order according to the year of construction. 9. Open the Used Cars form and change the word kilometer to km. 10. In the Used cars form, sort the records in ascending order by color. 11. Create the following five queries on the Used Cars table. All fields should be displayed. a) Query first-owner_ cheap: price between 7,000 and 30,000 b) Query few_km: all used cars with a number of kilometers below 50,000. c) Air conditioning query: all cars with air conditioning d) Blue_rot query: all cars that are either blue or red, sorted in descending order by color. e) Immediately_available query: All brands that are available after February 1, 2008, sorted by date, in ascending order. Print out this query. 12. Create two reports on the Used Cars table: a) Report Used Cars1: the first report should contain all fields and data records (landscape format, data layout: left-aligned classic and header and footer layout: standard); In the title, add used vehicle sales. b) Report used cars2: the second report should contain all fields and data records, grouped by color, sorted by year of construction, then by brand (landscape format, data layout: modern structure and header and footer layout: standard) 13.Close the database program .

www.p-merkelbach.de

-3-

© Merkelbach

Databases

Exercise 3 - Real Estate

1. Open the database program and create a new Sales database. 2. Put a new table in the

Draft view to:

3. Put on the primary key

the number field. .

4. Save the table under the

Name sale.

5. Create a simple form

Field name Number Property Living space Reason Location Price available

Field data type Auto value Text Number Number Text Currency Yes / no

Field size, 25 integer

Euro

(table, color: rice paper) for the table sale.

6. Save this form under the name Sales_Form. 7. Enter the data records (see page 2) either in the form or in the table. 8. Sort the records in ascending order by location. 9. Create a query on the Sales table that contains the following data: property,

Price, obtainable. . Save this query under the name Real Estate. .

10. Create a query on the Sales table in which all properties are displayed

whose price is between 100,000 and 200,000 and which are obtainable. The data should be displayed in ascending order (price). Save this query under the name Sofort_beziehbar.

11. Generate a report on the Sales table that includes all fields and records

(e.g. landscape, business) a. Add Real Estate - Sales as the heading. b. Format the headline in the report using Arial, 18 pt. Green. c. The entire list should be displayed in Arial 12 pt., Blue. The column headings should be black and italic. d. Save the report as Sales. e. Print out the report.

12. Close the program.

www.p-merkelbach.de

-4-

© Merkelbach

Databases

Exercise 4 Reports and Forms

1. Open the database treasure chest.

2. Delete the following reports: Report1 and Report2. 3. Then open the treasure chest and do the following formatting in Design view: a. Change heading to Meine Schatzkiste, Comic Sans MS, 22 pt., Font color purple, b. Remove the black lines above and below the report header. c. Format the page header: Arial, 10 pt., Black, italic d. Remove the number e field everywhere. Detail area: Arial, 12 pt., Dark blue f. Copy the heading into the footer of the report and reduce the font size to 14 pt. G. Change the top and bottom margins to 15 mm so that the report can be printed on two pages. 4. Copy the treasure chest report and paste it under it again with the new name my treasures. 5. Do similar formatting (like no.3) yourself by trying out different formatting (in the draft view) yourself. 6. Open the Special Items form and make the following changes: a. Delete the graphic and insert any other picture. b. Headline: any font, blue, 22 pt., Background color light blue, frame type: solid, frame width 3 pt., Frame color green c. In the footer, insert the page numbers n of m right-justified. 7. Close the database treasure chest.

8. Open the electrical appliances database. 9. Rename the Device input form to Electrical appliances and open it in the design view. Make the following changes: a. b. c. d. e. f.Detail area: green background color. Designation field Device number: Font Comic Sans MS, 10 pt., Dark green Transfer the format to the other designation fields. Text field device number: Font Arial, 12 pt., Black Transfer the format to the other text fields. Add the current date in the form footer.

10. Search in the form (menu: Edit / Search) for the entry Nilfisk and increase the price to 145 euros. 11. Search for Siemens and delete this data record (menu: Edit / Delete). 12. Save your changes and close the database program!

www.p-merkelbach.de

-5-

© Merkelbach

Databases

Exercise 5 - Create a database, run queries

1. Open the database association 2. Michi Müller's gender is entered incorrectly. Correct it! 3. Johnny Crested Grebe has moved. He now lives in Waldweg 7. Correct. 4. Reini Dorfer calls and asks whether he still has to pay anything. Write the answer here: .................. 5. How many members of the association live in the federal state of Vienna? .............. 6. How many members of the association do not live in Tyrol? ............... 7. How many female non-founding members does the association have? ................ 8. All founding members should be invited to a meeting. Create a query and save it under the name Foundation. 9. Create a report using the query table founding in which you list the names and addresses (with federal state) of the founding members (save name: founding members). The report should be grouped by state and sorted by surname and first name. The heading should read founding members: dark red, font size 26. 10. All members (table members) who live in the state of Salzburg are invited to a meeting. Create a query (zip code starts with 5) and save it under Land_Salzburg 11. All members born between 1960 and 1969 will be invited to a meeting. Create a corresponding query and save it under the name Meeting60 12. Create a report on this query Meeting60, which is sorted alphabetically by name and save it under Meeting60 13. All members who are younger than 20 years receive a letter. Create a suitable query and save it under the name Unter20. 14. Create a report on the query Under20, sorted alphabetically by name, and save it under Under20_List. The heading should read members under 20. Assign any font and color to the heading and underline it.

www.p-merkelbach.de

-6-

© Merkelbach

Databases

Exercise 6 Mail order or videos

1. Open the mail order database and create a new table in the design view with the following information: Field name No. Last name First name Post code Amount Field data type Auto value Text Text Text currency Field size, format 20 20 10 euros, 2 decimal places

2. Place the primary key on the No. field and save the table under the name Customer 2005. 3. Enter the following data records: No. 1 2 3 4 5 Last name Maier Maier Müller Oberthaler your last name first name Johann Maria Karin Michael your first name ZIP 4020 4020 1190 5440 5440 amount 145.90 146.90 985.90 149.90 200.00

4. Sort this table in ascending order according to the amount. 5. Create a query about the Customers_2005 table, in which all fields of the customers are displayed, whose zip code starts with 5. Save this query with the name Salzburg. 6. Create a one-to-many relationship between the Customers_2005 and Item tables. Use the No. fields from both lists. Enable referential integrity for this relationship. 7. Create another query in which you display the ZIP code field from the Customers_2005 table and the Article field from the Article table. Save this query under the name Customers_and_Article 8. Save your changes and close this database.

9. Open the Video2000 database. 10. In the Films table, look for the two records with the title Travel Magazine and change it to Travel Information. 11. Delete the record with the title: Snow White. 12. Insert the following data set: Title: Asterix in America, length: 100, keyword: animation, suitable for children: yes, taken on September 22nd, 2001 Open the draft view of the Films table! 13. Change the field size from Title to 40.

www.p-merkelbach.de

-7-

© Merkelbach

Databases 14. Delete the field miscellaneous in the table design. 15. Create a validation rule for the Length field that specifies that the film length should be greater than 10 min (> 10) 16. Create five queries on the Films table; save them under the given name: a) b) c) d) e) more than 45 minutes: All films that are longer than 45 minutes. Non-cartoon: All films that do not have cartoon as a keyword. New: All films that were recorded after January 1st, 2003. KrimiNeu: All crime novels that were recorded after 1.1.2001. Krimi_oder_Zeichentrick: Crime and cartoons should be displayed.

17. Create a report using the Films table with the following properties: o o o o o o All fields appear in the report. All films grouped by keyword, sorted by title Format: landscape format, graduated format Business Headline: film list, headline in Comic Sans MS, 26 pt., Font color red, page header: font color orange, bold, underlined, italic The first page of the report print

18. Create a report on the table over 45min with the following properties oooooooo The report contains the fields Title, Length, recorded on, sorted by length, in ascending order, portrait format, in table format, Compact Title: Long films in the report header right-justified insert the current date in the footer current date and page Delete n of m Align all columns in the report so that they are legible

www.p-merkelbach.de

-8-

© Merkelbach

Databases

Exercise 7 flight plan

1. Open the flight plan database and create a new table (draft view) with the following information: Field name Customer number Last name First name Post code Amount Field data type Text Text Text Number Currency Field size, format 10 20 20 Integer 2 decimal places

2. Put the primary key on the field customer number and save the table under the name customer. 3. Enter the following data records: Customer number Surname 1567 1568 2789 2790 3456 Huber Huber Maier Oberholenzer your surname first name Sabine Franz Herbert Max your first name ZIP 5020 5020 1190 5741 your ZIP amount 1450.00 1450.00 900.00 1300.00 2000.00

4. Sort this table in descending order by surname. 5. Create a query using the Customers table, in which all fields of customers whose customer number begins with 2 are displayed. Save this query under the name 2000. 6. Open the Destination table and delete the State column from the design view. 7. Delete the query Amsterdam_Morning. 8. Apply a form-based filter via the flight plan table, in which all flights with the departure location Salzburg and the airline Lufthansa are displayed. Save this query under Lufthansa_Sbg 9. Rename the query Tyrolean daily to Sbg_Wien_taily 10. Print the query Frankfurt_Afternoon in landscape format. 11. Create a 1: n relationship between the Categories and Flight Plan tables. Use the CATEGORY fields from both tables for the relationship. 12. Open the Flight Plans report and make the following changes (draft view): a) Set the left and right margins to 15. b) Heading: Arial 28 pt; Italic, underlined, text color blue, background color light gray c) Move the date from the footer to the report header (top right corner) 13. Save your changes and close the program.

www.p-merkelbach.de

-9-

© Merkelbach

Databases

Exercise 8 Customers - Invoices

1. Create a new database called Customers_Invoices. 2. Create two tables. Think about suitable field properties! (Use the field property TEXT for KNR) a) Customer table: KNR surname 2000 Hohenberger 2001 Gruber 2002 Hofer 2003 Höller 2004 Brugger 2005 Bauer 2006 Wagner 2007 Hainzer first name Josef Markus Martin Peter Peter Franz Michael Josef Strasse Kirchengasse 3 Waldweg 7 Fichtenweg 19 Markt 28 Tannenweg 5 Möwenstraße 11 Göllweg 19 Fasangasse 12 ZIP 5400 5440 5163 5440 5400 5163 5440 5163 City Hallein Golling Mattsee Golling Hallein Mattsee Golling Mattsee Category 1 3 1 2 1 2 1 2

Assign the primary key for the customer number (KNR)! b) Invoices table: ID 1 2 3 4 5 6 7 8 9 10 11 12 paid KNR amount Date No 7000 10/17/2005 2000 No 6000 10/17/2005 2003 yes 2001 14000 10/17/2005 No 2001 25,000 10/17/2005 No 2000 24,000 9/18 2005 yes 2000 30,000 10/17/2005 yes 15,000 10/17/2005 2002 no 2002 4,000 10/17/2005 no 55,000 10/17/2005 2000 yes 2000 60,000 8/28/2005 yes 2004 100,000 10/17/2005 yes 2004 9,500 10/17/2005

Assign the primary key to the ID field

3. Move the Category field in the Customers table in front of the Street field! 4. Change all column widths in the Invoices table to 20! 5. Create the following queries above the Customers table: a) Query Category_1: All customers with category 1 b) Query 5440: All customers with postcode 5440 6. Hide the fields City and Postcode in the query Category_1. 7. Create a 1: n relationship between the Customers and Invoices table! Use the KNR fields from both tables for this. 8. Create a query with the fields Last Name from the table Customers and Amount and Date from the table Invoices! Save the query under the name Kunden_Rechnungen 9. Think about 5 more useful queries! These queries should contain fields from the Customers and Invoices table! 10. Create a report grouped by zip code using the Customers table and use landscape format. Save the report with the name all_invoices. 11. Move the Number of Pages field from the footer to the report header. In the report, change the heading to All Bills.

www.p-merkelbach.de - 10 - © Merkelbach

Databases

Exercise 9 traveling

1.Open the database travel1 and create a new table according to the following information: Set the primary key to the field flight number and save the table under the name travel destination1. 2. Enter the following data records: Field name Field data type Field size, 3. Create a query about the format table members, which shows all data flight number car value of the members, which in the first half of 2004 (between category number integer 01.01.2004 and 30.06.2004) have joined the continent Text 25 Association. In the query design, move the First Name field before the Last Name field. Delete the Street and City fields from the query design. FlugNr Category Continent Sort the dates in ascending order according to the (car value) 1 Europe joining date. Save this query under (car value) 2 Africa with the name Members 2004-1 (car value) 3 North America 4. Create another query with the table (car value) 4 Asia travel destinations, which contains the fields destination, hotel, departure and duration for trips to New York (car value) 5 shows Australia or Florida. Save the query under (car value) 6 South America with the name USA. Sort the dates in ascending order of departure date. 5. Create a form for each table in this database (each with different properties, formatting and design) 6. Create a report for each table (each with different properties, formatting and design) 7. Close the database travel1. 8. Open the database travel2 9. Create a query above the table of travel destinations, in which all events that last longer than 7 days but shorter than 20 days are displayed. Show all fields. Sort the records in ascending order by travel destination. Save the query under the name Vacation Travel 10. In the Members table, after the Date field, add a new field called Fax. Complete the first five records by entering any fax number. 11. Close the travel database2.

12. Open the database travel3 13. Create a one-to-many relationship between the Members and Payments tables. Use the MemberID fields from both tables. Enable referential integrity for this relationship. 14. Delete the relationship between the Destinations and Members tables. Save and close the relationship window. 15. Open the Payments table and format the Amount column without decimal places.

www.p-merkelbach.de

- 11 -

© Merkelbach

Databases

16. In the Payments table in Design view, move the Member No. row before the date. 17. Clear the Events form. 18. In the Payments form, change the heading to Payments, font color green. 19. Open the table of travel destinations and print out the first 3 records. 20. Open the Travel Destinations table and define the following validity rule for the Duration field: The duration of the trip must be less than 20 days. (Draft view!) 21. Open the Members report and do the following formatting: a. Change the heading to Member List, font: Comic Sans MS 22 pt., Underlined, font color orange; Background color of the title block and background color of the report header light yellow b. In the report footer, add number of pages n of m right-justified c. Move the date from the footer to the report header (to the right). 22. Open the Category report and write the following text in the report footer: "I have finished my exercise" 23. Save the changes in the report and close the program.

www.p-merkelbach.de

- 12 -

© Merkelbach

Databases

Exercise 10 Football World Cup 2006

Create, format tables, ... 1. Open the database wm2006 and create a new table according to the following information: Set the primary key to the ID number field and save the table under the name Groups. Enter the following data records: ID-No State Group 1 Germany A 2 England B 3 4 5 6 3. 4. 5. 6. Argentina Angola Ghana Brazil C D E F Field name Field data type Field size, format ID No. State Group Auto value Text Text 30 10

2.

Drop the table nothing_new. Open the table England and navigate to data record 5. Insert the first name David. Open the Premium table and format the Premium column without decimal places

In the Argentina table in Design view, move the First Name row before the Name field. 7. Open the Germany table and print out the first 3 records. 8. 9. Open the Premium table and define the following validity rule for the Premium field: The premium must be less than 200. (Draft view!) Open the table SC Golling. After the Name field, add a field titled Nickname (Text, 50). (Draft view!) Define a validity rule for the year of birth field so that only those born after 1990 are allowed. In the Germany table, define a validity rule for the Nationality field that only contains the entry ,, D & quot; allows. In the Netherlands table, assign an index to the Position field that allows duplicates. Find the first name Fritz in the Brazil table and replace it with Juan. Look for Oliver Kahn in the Germany table and replace him with Jens Lehmann.

(note the corresponding fields!)

10. 11. 12. 13.

Queries 14. Create a query on the Brazil table that displays all the data for members who were born in the years 1976 to 1980 (between 1976 and 19801). Sort the dates in ascending order by year of birth. Save this query under the name 1976_ bis_1980. Change the query Netherlands_Stürmer so that only players with position 4 are displayed. Sort this query in ascending order by name and save the changes. Change the query Germany_Vereine so that all players from the clubs Bayern Munich or Schalke 04 are displayed.

15.

16.

www.p-merkelbach.de

- 13 -

© Merkelbach

Databases

17. Change the query not_Chelsea so that only players who do not belong to the Chelsea club are displayed. Forms 18. Create a form (one column) for the table England. Save this form with the name England and make the following changes in the design view: a. Add the heading ,, England & quot; in red font, font size 14 pt. b. Add the graphic flagge_england to the right margin of the form. c. Display the Name field in red and bold. 19. Change the page layout in the France form to landscape format and all margins to 30. Relationships, queries ... 20. 21. Delete the relationship between the tables SC Golling and Premium. Save and close the relationship window. Create a 1: n relationship between table position 1 and Brazil. Use the ID-Nr field from the Brazil table and the Category field from the Position1 table. Enable referential integrity for this relationship. Query the Brazil and Position1 tables using the Name fields from the Brazil table and Position from the Position1 table. Save this query under the name Brazil_Abfrage. Filter 23. Open the Brazil table. Apply a selection-based filter that shows all the players from the Real Madrid club. Enter the number of data records filtered in this way in the Brazil table as a new data record (Name field).

22.

Open the England table. Apply a form-based filter to the Chelsea club. Save the changes in the England table. 25. Apply a selection-based filter for the Year of Birth field to 1991 above the SC Golling table. Delete all records with the year of birth 1991 Reports 26. In the Brazil report, change the heading to Football Wizard and move it towards the middle. 27. Copy this heading into the report footer and change the font size to 14pt. 28. 29. In the bottom left of the report, insert the current date and the page number at the bottom right. Generate a report on the Argentina table, grouped by position, sorted in ascending order by name. Do NOT display the current date and number of pages in the footer. Create a report on the Premium2 table. Use the Player and Bonus fields for this. Group the report by the Player field and calculate the mean. (Summary options). Save the report under the name Premium2_ Average. Create another report on the Premium2 table. Use the Player and Bonus fields for this. Group the report by the Player field and calculate the total. (Summary options). Save the report under the name Premium2_ Total.

24.

30.

31.

www.p-merkelbach.de

- 14 -

© Merkelbach

Databases

Exercise 11 - Database Modeling

Information The Stickler horse farm in Puchberg has one of the largest Icelander breeders in Austria. In the meantime, the manual administration is too cumbersome and a database program is to be introduced. In a first step, a rough analysis was carried out and it was determined which information should be stored in this database. · · · The name and date of birth of every Icelandic pony must be saved. Every horse has a supervisor. For reasons of capacity, however, there is not a separate supervisor for each horse, but one supervisor is responsible for several horses. The supervisor's first and last name and address are relevant. Some horses take part in tournaments. Tournaments take place several times a year and good horses are used depending on availability. The place and date of the tournament are important. The placement of the horse is to be saved in the database after participation in the tournament. Horses can be rented to experienced riders with a rider's license. The rider's passport number, first name and surname should be saved. For the rental, it is important on which day a horse was rented and at what time (from, to). Of course, a rider can rent a horse more often and a horse rides with different riders.

·

Exercise 1 Entity-Relationship-Model On a blank piece of paper, create an Entity-Relationship-Model in which you correctly represent the above facts. · · · When storing the address, note that only one value can be stored in a cell in relational databases. Assign a value for each entity that uniquely defines a data record and mark it accordingly. Establish the quantitative relationship between the entities.

Exercise 2 Relational database model Convert the ER model into a relational database model in 3. normal form. Mark primary and secondary keys according to the notation of database theory.

www.p-merkelbach.de

- 15 -

© Merkelbach