CCSU Spring, 2001 Project 2: Building a MySql Database. Course: CS 460 Posting Date: March 19, 2001 Due Date: May 7, 2001 Description: Now that you know some database theory, it is time to build a real database in a real database management system. Building a database involves five basic steps: 1. Designing the database schema, that is deciding on the structure of the tables. In this project, the design work will already have been done for you. Designing a database is covered in several of the pencil-and-paper homework problems. 2. Documenting the database, that is creating a DATA DICTIONARY as explained in the chapter of Roger Jenning's book handed out in class (Using Access 97, chapter 23). You will not only get to do this, you will be REQUIRED to do this. 3. Creating the tables using SQL CREATE TABLE statements. You will get to do several of these, one for each table in your database. 4. Populating the database. This means putting correct data into the tables using SQL INSERT INTO statements. You will get to do several of these too, but to minimize the amount of typing, you will only insert a small amount of data into each table. 5. Testing the database. This means issuing sample SQL queries, and verifying that the results are reasonable, and that users can obtain the information they are likely to need from your database. You will get to do this. Of course, building a database is an iterative process. Usually, database designers and programmers go through steps 1 through 4, but at first only insert a small amount of test data in step 4. Then they thoroughly carry out step 5, with users standing by their side to suggest what to ask the database for and to judge whether the database responds in a correct and helpful manner. Using the test results, the database designers may discover that they have to correct the design because they misunderstood some part of the basic business logic of the organization they are designing for. The designers then repeat steps 1 through 4, incorporating the corrections, and repeat step 5 as above. Only when it is virtually certain that the design is fully correct do the database designers and programmers fully populate the database. When database designers and programmers fully populate a database, they typically do not type the data in themselves. Instead, they obtain the data from pre-existing files, or from files prepared by the employees of the organization they are designing and programming for. They then use various tools, or utility programs they themselves have written, to automatically insert the data into the correct tables in the database. Normally, the basic structure of a program to insert data into a given table looks like this: while there is more data do // records are stored one to a line in a comma separated // or tab separated database file read the next line of the comma separated or tab separated database file; divide the line into fields using the field separator characters (commas or tabs); insert the data into the table using an SQL INSERT INTO statement; end In this project, you will only have a small amount of data in the first place, so you can insert all of it the first time through using INSERT INTO statements. This has the virtue that you will get lots of practice using INSERT INTO statements. As the design is certified 100% correct, you will not need to iteratively repeat the steps of the design process, unless you discover that you have made an error. Of course, you need to thoroughly check your work. What To Use: Use your assigned team MySql database on the CS department's student linux server, earth.ccsu.ctstateu.edu, (no comma at the end of the URL) and MySqlJavaClient (available for download from the course Web page). Do not use another MySql client because you probably won't be able to install it in the shared laboratory environments we must use for demonstrations. Use the database schema and data for the shipping line database in the chapter of Roger Jenning's book handed out in class (Using Access 97, chapter 23). Use the DATA, and ONLY THE DATA, in the chapter so everyone will have a consistent dataset. Use the FINAL database schema, and ONLY THE FINAL database schema, for the fully normalized database so everyone will have a consistent and correct database design. (Hint: The FINAL TABLE DESIGNS tend to occur towards the end of the chapter.) Detailed Specifications: (1) Work on this project in your assigned team of two. (2) You MUST follow the directions in "What To Use" above. (3) You MUST carry out all but step (1) of the basic steps listed above for building a database. That is, you must carry out steps (2), (3), (4) and (5) as listed above. Although you do not have to carry out step (1), you do have to draw Entity-Relationship diagrams for the final database schema so you can include them in your project report (see below). (a) Draw Entity-Relationship diagrams for the final database schema using the notation given in the text rather than the notation Jennings uses. (For example indicate many-one relationships by arrows as the text does.) E-R diagrams may be NEATLY drawn by hand, or by using charting software. If you draw by hand, use a PLASTIC TEMPLATE, a RULER, and a VERY SHARP PENCIL. (b) Create the data dictionary in the format given by Jennings. (c) Create the tables using SQL CREATE TABLE statements. (d) Enter the data using SQL INSERT INTO statements. (e) Test the database using a number of SQL SELECT-PROJECT-JOIN queries. Implement joins using WHERE clauses as explained in class. Design your own SQL queries. Part of your grade will depend on how well you choose appropriate queries, so don't expect to do well if you have only one query of the form SELECT * FROM MYTABLE. (f) Copy representative statements under (c), (d) and (e) from the query window of MySqlJavaClient using CTRL-C, and paste them into a text document so you can include them in your project report (see below). As the Edit menus of MySqlJavaClient do not yet work, you may wish to refer to the Readme.txt file for more information about how to copy and paste. (4) You MUST hand in a typed and stapled project report, as described in the General Project Instructions (available from the course Web page). Your project report is to include (a) A title page as described in the General Project Instructions. (b) One or more introductory paragraphs written in proper English, as described in the General Project Instructions. (c) Copies of your design documentation, including E-R diagrams and a data dictionary. (d) Copies of representative SQL statements you used, as described above. As MySqlJavaClient will probably not include copying or printing results in time for this project, you do not have to include copies of the results of queries. You should say, however, whether the SQL statements performed successfully. (5) You MUST demonstrate your project to me, as described in the General Project Instructions. In addition (a) Your demonstration should begin with a walk-through of your design documentation. (b) Your demonstration should include sample SQL SELECT-PROJECT-JOIN queries. Implement joins using WHERE clauses as explained in class. You may use the queries included in your project report. (c) Your demonstration should include (i) updating a record using an SQL UPDATE statement. (ii) inserting a new record into a table using an SQL INSERT INTO statement. (iii) deleting a record from a table using an SQL DELETE FROM statement. (d) Your demonstration should finish with a summary of why your database implementation is the very finest one money can buy. You may even talk about stuff you didn't do yourself, such as the normalizations done for you by Jennings. (e) Your demonstration should take approximately 30 minutes. (5) Because this is a rehearsal for the real world, dress appropriately. Men should wear a coat and tie. Women should wear appropriate business attire. (We will relax the real world requirements just a bit. For men, sport coats are OK; business suits are not needed but always may be worn. I won't look at your shoes if you don't look at mine. A similar relaxation applies to women.) Even I will make the sacrifice of wearing a (sport) coat and tie to set an appropriate example. As there is no separate examination week for evening courses, demonstrations will take place during the last two weeks of class. Your team will be ASSIGNED a time to give its demonstration. Because this is a large class and there are many teams, do not expect to give your demonstration at the end of the second week, instead plan to give your demonstration at the beginning of the first week.