< 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 visible during 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.

 

ACTIVITY

 

So, in this activity, you and your group members are required to analyze a web-based application called Court Reservation System (CRS) own and managed by UUM University. The interfaces (screenshots) of the system are shown in the following section. Based on the given information, all groups have to submit the following completed tasks:

 

  1. Entity Relationship Diagram for CRS.

  2. SQL commands for creating the tables.

  3. Screenshot all the tables including the data (at least FIVE (5) records for each table) of CRS database. You are advised to use the real data.

  4. SQL commands for viewing the related information for each screen.

  5. The results of task 4.

 

Guidelines:

 

  1. Designing Tool : Use DBDesigner4

  2. Database : Use MySQL

 

The following screenshots are the web interfaces for CRS system.

 

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design aspects (MySQL) screenshots

 

 

CONTENTS

  1. BASIC ANALYSIS

  2. BUILDING THE ERD DIAGRAM

  3. CREATING THE RELATIONAL MODEL

  4. BUILDING DATABASE MODEL USING MySQL AND DBDESIGNER 4

  5. EXECUTING MySQL SCRIPTS USING DBDESIGNER 4

  6. CREATING DATABASE MODEL (REVERSE ENGINEERING THE DATABASE TABLES)

  7. INSERTING SAMPLE REAL DATA

  8. VIEWING THE TABLES DATA

  9. USING SQL DML AGAINST THE DATABASE

  10. CONCLUSION

  11. REFERENCES

 

BASIC ANALYSIS

 

We start by analyzing the web site based on the observation of the form fields, the data that need to be filled in and the web pages. We found that there are several issues need to be sorted out and summarized the main issues as follows:

 

  1. If we based on just the matric number, only student can make reservations. What about UUM staff and outsiders that don’t have matric number?

  2. If we restrict the reservation for the student only, then the occupation column can be dropped because all or majority should be a student. Furthermore the IC number (similar to SSN number) may be NULL for foreign students.

  3. If we open to the outsider, then the IC number must be included and matric number cannot be used as a candidate key. However foreign students still can fill in the IC numbers.

  4. Foreign student cannot fill in the IC number column instead they may fill in Passport number that needs an extra column.

  5. By assuming the system only for UUM student, then the matric number and email address can be candidate keys and the system should be rename to "UUM Student Only Court Reservation System". In this case we can drop the occupation and IC columns; because all students supposed to be a student (full or part timer) and foreign student doesn’t have IC number or we need to permit NULL values for IC Number column. Outsiders and staff need to do all the reservation task manually making the CRS not so usable.

  6. By assuming the CRS is for all (student, staff and outsiders), the matric number cannot be used as a candidate key. In this case we need another attribute to be considered as candidate key such as UserID. Without providing another column for Passport number for foreign students, we can share it with IC number column. However this obviously violates the 1NF, the atomicity of the cell. Outsiders also may not have email addresses. In this case also the users may have generalization entity in the ERD diagram.

 

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.

 

Student

matricNo

emailAddr

name

gender

doc

race

contactNo

address

psword

 

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

 

matricNo

emailAddr

name

gender

dob

race

contactNo

address

psword

86778

saad@uum.edu.my

Saad bin ali

male

23-10-1970

Malay

012-3452321

123 Kolej Maybank, UUM

1234&$

89001

maidah@uum.edu.my

Maidah bt osman

female

10-02-1980

Malay

013-6753412

214 Kolej Guthrie, UUM

5%$#@1

85789

salmi@uum.edu.my

Salmi bt khalid

female

12-07-1976

Malay

016-6542378

201 Kolej Proton, UUM

yu65#$

84521

fong_32@yahoo.com

Fong Ah Kui

female

25-09-1978

Chinese

019-3823419

321 Kolej Maybank, UUM

h8$&j#

87991

santini@gmail.com

Santini a/p Kalupa

female

21-01-1960

Indian

016-7682314

103 Kolej Guthrie, UUM

k6y&$*

89667

salman@yahoo.com

Salman b. Ghani Khan

male

04-05-1969

Malay

013-6752312

103 Kolej Proton, UUM

rt5$&8

87669

fakhrul@uum.edu.my

Fakhrul ibni Sayuti

male

17-01-1972

Other

017-3416745

121 Kolej Maybank, UUM

7u5r%@

 

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

 

ccNumber

ccName

ccType

ccExpiryDate

ccIssuerBank

5422240032001745

Sarip Abdul bin Ghani

VISA

03/2009

Maybank

3400220043007896

Saripah bt Malik

Master

10/2010

CIMB Bank

2433340022005674

Hamzah bin Ismail

VISA

12/2009

Public Bank

3400240033223452

Irene Khoh Eng

Master

07/2010

AMM Bank

5600340022331233

Satiya a/l Maniam

VISA

02/2010

RHB Bank

4500230034002314

Ong Ka Ting

Master

05/2012

Bank Islam

 

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

 

status

available

booked

 

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

 

reserveDate

timeStart

timeEnd

05/08/2007

08:00 am

10.00 am

05/08/2007

10.00 am

12.00 pm

05/08/2007

04.00 pm

06.00 pm

05/08/2007

06.00 pm

08.00 pm

05/08/2007

08.00 pm

10.00 pm

 

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

 

Court

 

courtType

courtNo

Badminton

A

Tennis

A

Badminton

C

Sepak takraw

C

Tennis

B

Squash

B

Ping pong

C

 

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

 

equipType

equipQty

ping pong bat

2

badminton rackets

4

tennis rackets

4

tennis ball

10

takraw ball

4

ping pong ball

10

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

One court type of course has many types of equipment.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

The following is an ERD diagram of our first attempt.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 7

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 8

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 9

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 10

 

Next, we can start designing the table model.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 11

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 12

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 13

 

The following Figure shows a completed Student table model.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 14

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 15

 

The following Figure shows the Payment table properties.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 16

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 17

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 18

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 20

 

Next we copy and paste the SQL script into the editor and then click the Execute (Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots ) button. We repeat this task for all the remaining tables.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 22

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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%@');

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 27

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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.

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 29

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 30

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 31

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 32

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 33

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 34

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 35

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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;

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 37

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 38

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 39

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 40

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 41

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

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#');

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 43

 

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

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

 

Figure 44

 

SELECT equipType, equipQty FROM Equipment WHERE equipQty = 4;

 

Step-by-step tutorial on using DBDesigner to analyze a web portal from the database design (ERD) aspects (MySQL) screenshots

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 >