<DBDesigner: Install, Test & Use |Java & Friends Activities |From ERD to Relational Model (Tables) Part 1 >

USING DBDESIGNER:

ANALYZING DATABASE OF THE WEB APPLICATION (MySQL)

Database design stage is one of the important phases in the information system development that will determine the success and the quality of the database application or the finish product. Although the database can be visibleduring the analysis stage of the information system development (Plan, Analyze, Design and Implement), in this case we are analyzing in the reverse manner, which the database application development process already completed. We will try to apply all the techniques and rules that normally used during the design stage.

At this stage we try to use the Normal Form rules whenever possible. We suggest a name to a table, determine the entity types, attributes and then populate some sample data into the table.

By analyzing the New Registration web page, the following is the Student entity type with attributes. The matricNo and emailAddr can be candidate keys.

The following is the Student table populated with sample data. At first glance, the race and gender look redundant in this table however this non keys information is not significant here and does not create update anomalies as a reason to provide separate tables. For example, gender only consists of two values of data and the race consists of several values of data only. These values of data will not change forever. However, when we need to edit the value of male or female there will be a lot of records to be updated. We will re-analyze this issue later after building the ERD diagram if needed. This table is linked to the Payment table. Only student that already made payment can place their reservation.

Student

The following is a payment table and a sample data. The ccNumber can be a candidate key and this table is linked to the Reservation table, once payment has been made, the student can place a reservation.

Payment

The reservation made (courtType, courtNo, reserveDate, timeStart and timeEnd) can be indicated by a status attribute. The status will be used to set the availability of the court on the certain time and date. The Reservation web page is similar to the Cancel Reservation page so the cancellation can be done using the UPDATE command on the status of the reservation. The reservation is linked to the Court and CourtTimeTable information. Just with the one attribute, the Reservation may be a relationship with an attribute of the Court and CourtTimeTable.

Reservation

The Court Table web page contains information on court type, day and time schedules and court number. We separate these three information into two entity types: CourtTimeTable and Court where Court will hold court type and court number. The tables have been separated to avoid update anomalies. We divided the time column into two columns to avoid the 1NF violation.

The following is a sample time and date data provided for the CourtTimeTable table. From the web pages, the CourtTimeTable is linked to the Court, Reservation and Equipment tables. We can see that the relationship between Court and CourtTimeTable is many-to-many (M-N).

CourtTimeTable

The following is a Court table and a sample data. This table is linked to Reservation and CourtTimeTable tables.

Court

The following is an equipment table and a sample data. This table is linked to the CourtTimeTable and Court tables (courtType) because every courtType will have their own specific equipment type.

Equipment

The About Us and Term and Condition web pages just contain static html texts and the Report page contains derived attributes that we can ignore it.

BUILDING THE ERD DIAGRAM

In this database design reverse engineer stage we will try to create an ERD diagram and refining the related things from the previous created entity types. We are assuming that this CRS is for UUM students only. The matric number and email address can be candidate keys. The IC number column can be removed else NULL value must be permitted or we can retain the IC number column with extra Passport Number column and both must enable NULL values. Matric number will be used as login username.

Upon completion of the registration, student must make payment before permitting the reservation. One student can make many payments for different reservations.

After payment, one user can make many reservations. It is obvious when there is a tournament or competition.

One reservation may book many courts and at different time schedules. There are many court types and many time schedules. When combining the time and date, there are even many more time schedules. This M-N relationship with an attribute can be converted to two 1-M relationships and the conversion process will be shown in next section. The Reservation contains date, time, court type and court number. This obviously resembles the CourtTimeTable and Court attributes.

One court type of course has many types of equipment.

The following is an ERD diagram of our first attempt.

CREATING THE RELATIONAL MODEL

From the ERD diagram we try converting the previously created ERD to Relational Model. The following are the relational model for the previously created ERD diagram.

Student(matricNo, emailAddr, name, gender, dob, race, contactNo, address, psword)

Primary Key             matricNo

Foreign Key             -

 

Payment(ccNumber, ccName, ccType, ccExpiryDate, ccIssuerBank, amount, matricNo)

Primary Key             ccNumber

Foreign Key             matricNo       References  Student

 

CourtTimeTable(reserveID, reserveDate, timeStart, timeEnd, ccNumber)

Primary Key             reserveID

Foreign Key             ccNumber    References  Payment

 

Court(courtID, courtType, courtNo)

Primary Key             courtID

Foreign Key             -

 

Reservation(reserveID, courtID, status)

Primary Key             reserveID, courtID

Foreign Key             reserveID                  References  CourtTimeTable

Foreign Key             courtID                    References  Court

 

Equipment(equipNo, equipType, equipQty, courtID)

Primary Key             equipNo

Foreign Key             courtID          References  Court

BUILDING DATABASE MODEL USING MySQL AND DBDESIGNER 4

Next, we convert the relational model to MySQL scripts. First of all we create a database named crssystemdb using MySQL Command Line Client.

Figure 16

Then for the rest of the activities we use DBDesigner 4. Using DBDesigner 4 we connect to the empty crssystemdb database and in the Design Mode we start designing the tables and creating the relationship for the database model. Some screen snapshots for these tasks are shown in the following Figures.

Figure 7

Figure 8

Make sure the DBDesigner was connected to the database as can be seen at the bottom right of the DBDesigner window.

Figure 9

To create a new table, select and drop or click the table icon (shown below) and click on the editor designer.

Figure 10

Next, we can start designing the table model.

Figure 11

Select and right-click the table. Select the Edit Object context menu.

Figure 12

The following is the Table Editor page. We fill in all the Student table’s information.

Figure 13

The following Figure shows a completed Student table model.

Figure 14

The table model’s properties also can be accessed from the Model window shown in the following Figure.

Figure 15

The following Figure shows the Payment table properties.

Figure 16

Next, we are ready to create a relationship (1-M) between the Student and Payment tables.

Figure 17

Select, drag and drop the 1-M icon between the tables as shown in the following Figure.

Figure 18

Figure 19

EXECUTING MySQL SCRIPTS USING DBDESIGNER 4

After completing these two table models, we open the Query Mode and execute the remaining MySQL scripts to create a database model. Switching to the Query Mode, we execute the MySQL scripts (these scripts generated from the relational model). We found this is easier and faster compared to building the model in the Design Mode. We have to make sure there are no error(s) generated for all the tasks. At this stage, we must take care the order of the tables to be generated.

CREATE TABLE Student(

     matricNo  INTEGER(10),

     emailAddr VARCHAR(30),

     name      VARCHAR(50),

     gender    CHAR(10),

     dob       DATE,

     race      VARCHAR(20),

     contactNo VARCHAR(15),

     address   VARCHAR(50),

     psword    VARCHAR(10),

     CONSTRAINT PKmatricNo PRIMARY KEY (matricNo)

) type=innodb;

 

CREATE TABLE Payment(

     ccNumber      BIGINT(16),

     ccName        VARCHAR(30),

     ccType        VARCHAR(20),

     ccExpiryDate  VARCHAR(7),

     ccIssuerBank  VARCHAR(30),

     matricNo      INTEGER(10),

     amount        DOUBLE(10,2),

     CONSTRAINT PKccNumber PRIMARY KEY (ccNumber),

     CONSTRAINT FKmatricNo FOREIGN KEY (matricNo) REFERENCES Student(matricNo)

)type=innodb;

 

CREATE TABLE CourtTimeTable(

     reserveID BIGINT(10) AUTO_INCREMENT,

     reserveDate   DATE,

     timeStart TIME,

     timeEnd   TIME,

     ccNumber  BIGINT(16),

     CONSTRAINT PKreserveID PRIMARY KEY (reserveID),

     CONSTRAINT FKccNumber FOREIGN KEY (ccNumber) REFERENCES Payment(ccNumber)

)type=innodb;

 

CREATE TABLE Court(

     courtID   BIGINT(10) AUTO_INCREMENT,

     courtType VARCHAR(40),

     courtNo   CHAR(2),

     CONSTRAINT PKcourtID PRIMARY KEY (courtID)

)type=innodb;

 

CREATE TABLE Reservation(

     reserveID     BIGINT(10) AUTO_INCREMENT,

     courtID       BIGINT(10),

     status        VARCHAR(20),

     CONSTRAINT PKreservation PRIMARY KEY (reserveID, courtID),

     CONSTRAINT FKreserveID FOREIGN KEY (reserveID) REFERENCES CourtTimeTable(reserveID),

     CONSTRAINT FKcourtID FOREIGN KEY (courtID) REFERENCES Court(courtID)

)type=innodb;

 

CREATE TABLE Equipment(

     equipNo       BIGINT(10) AUTO_INCREMENT,

     equipType     VARCHAR(40),

     equipQty      BIGINT(10),

     courtID       BIGINT(10),

     CONSTRAINT PKequipNo PRIMARY KEY (equipNo),

     CONSTRAINT FKcourtID1 FOREIGN KEY (courtID) REFERENCES Court(courtID)

)type=innodb;

Firstly we switch to Query Mode.

Figure 20

Next we copy and paste the SQL script into the editor and then click the Execute ( ) button. We repeat this task for all the remaining tables.

Figure 21

CREATING DATABASE MODEL (REVERSE ENGINEERING THE DATABASE TABLES)

After completing all the table creation, we reverse engineer those tables generating the database model.

Figure 22

Figure 23

In the Reverse Engineer window, we do the following tasks.

1. Select all the tables in the Tables list by selecting the tick boxes.

2. Select the Build Relation tick box and the Build Relations based on Primary Keys.

3. Select the Use Datatype substitution to convert all non standard data types.

There will be message box complaining errors, if there are any error(s) such as inconsistencies.

Figure 24

The database model for CRS system is shown below. The M-N of the Reservation has been converted properly to a table as seen in the CRS database model in the following Figure.

Figure 25

The notation used in this model is a Crows Foot. You can change to other notation from the Display > Notation menu as shown below.

Figure 26

INSERTING SAMPLE REAL DATA

Finally we insert sample real data into those tables using the MySQL scripts and DBDesigner 4 as done before. We have to take note on the order of the sample data insertion into the table so that not to violate the foreign keys constraints.

Student Table MySQL Insert Script

INSERT INTO Student VALUES('86778','saad@uum.edu.my','Saad bin ali','male','1970-10-23','Malay','012-3452321','123, Kolej Maybank, UUM','1234&$');

INSERT INTO Student VALUES('89001','maidah@uum.edu.my','Maidah bt osman','female','1980-02-10','Malay','013-6753412','214, Kolej Guthrie, UUM','5%$#@1');

INSERT INTO Student VALUES('85789','salmi@uum.edu.my','Salmi bt khalid','female','1976-07-12','Malay','016-6542378','201, Kolej Proton, UUM','yu65#$');

INSERT INTO Student VALUES('84521','fong_32@yahoo.com','Fong Ah Kui','female','1978-09-05','Chinese','019-3823419','321, Kolej Maybank, UUM','h8$&j#');

INSERT INTO Student VALUES('87991','santini@gmail.com','Santini a/p Kalupa','female','1960-01-21','Indian','016-7682314','133, Kolej Guthrie, UUM','k6y&$*');

INSERT INTO Student VALUES('89667','salman@yahoo.com','Salman b. Ghani Khan','male','1969-05-04','Malay','013-6752312','103, Kolej Proton, UUM','rt5$&8');

INSERT INTO Student VALUES('87669','fakhrul@uum.edu.my','Fakhrul ibni Sayuti','male','1972-01-17','Other','017-3416745','121, Kolej Maybank, UUM','7u5r%@');

Figure 27

After the insertion we do verification by viewing all the inserted data for the table by running the SELECT statement.

Figure 28

Payment Table MySQL Insert Script

INSERT INTO Payment VALUES('5422240032001745','Saad bin ali','VISA','03/2009', 'Maybank','86778', 40.00);

INSERT INTO Payment VALUES('3400220043007896','Maidah bt osman','Master', '10/2010','CIMB Bank','89001', 100.00);

INSERT INTO Payment VALUES('2433340022005674','Salmi bt khalid','VISA','12/2009', 'Public Bank','85789',40.00);

INSERT INTO Payment VALUES('3400240033223452','Fong Ah Kui','Master','07/2010', 'AM Bank','84521',70.00);

INSERT INTO Payment VALUES('5600340022331233','Santini a/p Kalupa','VISA', '02/2010','RHB Bank','87991',70.00);

INSERT INTO Payment VALUES('4500230034002314','Salman b. Ghani Khan','Master', '05/2012','Bank Islam','89667',40.00);

CourtTimeTable Table MySQL Insert Script

INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-05','08:00','10:00','5422240032001745');

INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-07','10:00','12:00','3400220043007896');

INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-05','16:00','18:00','2433340022005674');

INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-08','18:00','20:00','3400240033223452');

INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-08','20:00','22:00','5600340022331233'); 

INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-09','18:00','20:00','4500230034002314');

Court Table MySQL Insert Script

INSERT INTO Court(courtType, courtNo) VALUES ('Badminton','A');

INSERT INTO Court(courtType, courtNo) VALUES ('Tennis','A');

INSERT INTO Court(courtType, courtNo) VALUES ('Badminton','C');

INSERT INTO Court(courtType, courtNo) VALUES ('Sepak takraw','C');

INSERT INTO Court(courtType, courtNo) VALUES ('Tennis','B');

INSERT INTO Court(courtType, courtNo) VALUES ('Squash','B');

INSERT INTO Court(courtType, courtNo) VALUES ('Ping pong','C');

 Reservation Table MySQL Insert Script

INSERT INTO Reservation VALUES (1,1,'Available');

INSERT INTO Reservation VALUES (2,2,'Booked');

INSERT INTO Reservation VALUES (3,3,'Booked');

INSERT INTO Reservation VALUES (4,4,'Booked');

INSERT INTO Reservation VALUES (5,5,'Booked');

INSERT INTO Reservation VALUES (6,6,'Available');

Equipment Table MySQL Insert Script

INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('ping pong bat',2,1);

INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('badminton rackets',4,2);

INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('tennis rackets',4,3);

INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('tennis ball',10,4);

INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('takraw ball',4,5);

INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('ping pong ball',10,6);

VIEWING THE TABLES’ DATA

We can also view the inserted data through the Edit Table Data context menu as shown below.

Figure 29

Figure 30

To switch to other table, select the drop down control shown in the following Figure.

Figure 31

Figure 32

Figure 33

Figure 34

Figure 35

Figure 36

USING SQL DML AGAINST THE DATABASE

After successfully inserting sample data, we do some queries using DBDesigner against those tables to check any consistencies, redundancies and the robustness.

SELECT name, ccNumber, ccExpiryDate FROM Student, Payment GROUP BY name;

Figure 37

SELECT courtType, courtNo, status FROM Court, Reservation GROUP BY courtType;

Figure 38

SELECT name, gender FROM Student WHERE gender = 'male';

Figure 39

SELECT name, gender FROM Student WHERE race = 'malay';

Figure 40

SELECT ccName, amount FROM Payment WHERE amount <= 70;

Figure 41

SELECT reserveDate, timeStart FROM CourtTimeTable WHERE timeStart != '10:00:00';

Figure 42

INSERT INTO Student VALUES('88798','Chang@uum.edu.my','Chang Ah Soon','male','1977-06-13','Chinese','019-5453243','207, Kolej TM, UUM','76%$j#');

Figure 43

UPDATE Student SET emailAddr = 'chang_soon@uum.edu.my' WHERE matricNo = '88798';

Figure 44

SELECT equipType, equipQty FROM Equipment WHERE equipQty = 4;

Figure 45

CONCLUSION

The analysis of the web site from the database design aspects done in this project is based on what we have learned before. It can be considered a reverse engineering process, analyzing the finished product, a web site and then finding the flaws and inconsistencies. In this case, the important parts in the database design include:

1. Finding and listing the entity types, attributes and relationships.

2. Building the Entity Relationship Diagram (ERD).

3. Converting the ERD to Relational Model.

4. Preparing the SQL scripts from the Relational Model (tables).

5. Inserting sample data.

6. Testing using the SQL Database Manipulation Language (DML) against the database.

7. Applying normalization rules for no. 1, 2 and 3 as needed.

We are not using the normalization techniques intensively in this project because the mandatory rule is just 1NF and whenever the situations exist. In order to produce a very good design, optimized, performance wise and using minimum resources, the actual database design process must be iteratively done. We also found that at the beginning stage a lot of common senses must be taken into consideration such as during the ERD building. This must be tightly bound to the purpose of the database system that is going to be developed, the users that will use the system and the database maintenance.

Finally we have very good opportunities in applying what we have learned in this subject from concepts, principles and implementations. Other than the theoretical topics we also gain a lot of experiences using software and tools such as NetBeans, DBDesigner, MySQL and Derby databases.

REFERENCES

1. Database Systems: A Practical Approach To Design, Implementation And Management 4/e 2007, by Thomas Connolly and Carolyn Begg, Pearson Education.

2. Database Design, Application, Development & Administration, 3/e 2007 by Michael V. Mannino, McGraw-Hill.

You may want to see an example and learn on the data collection and analysis during the Planning and Analyzing stages of Information System Development.

<DBDesigner: Install, Test & Use |Java & Friends Activities |From ERD to Relational Model (Tables) Part 1 >