Database Systems: Designing and Generating Reports using SQL and Java | Slides Introduction to Database Management Systems

SQL and Java G52DBS – Database System s

www.cs.nott.ac.uk/~smx/DBS

E/R Diagram

Each student is enrolled in a

number of modules. The

modules each have a title,

code, and credit weighting

and students have IDs, first

and last names. For each

enrolment a student has we

record the year that the

module was taken and the

mark achieved. The exam

board also needs to know

what year a student is

currently in

Student

Enrolment

Module

First

Last

Year

Grade

Year

Title

Code

Credits

SQL and Java G52DBS – Database System s

www.cs.nott.ac.uk/~smx/DBS

E/R Diagram to Tables

• We now make tables

for each entity

•Student

• Enrolment

•Module

• The attributes

become columns

• 1:M relationships are

foreign keys

• Student table:

stuID, stuFirst, stuLast,

stu Year

• Module table:

modCode, modTitle,

modCredits

•Enrolment table:

stuID, modCode,

enrMark, enrYear

SQL and Java G52DBS – Database System s

www.cs.nott.ac.uk/~smx/DBS

Keys

• We declare Primary

keys for each table

• Student: {stuID}

• Module: {modCode}

• Enrolment: {stuID,

modCode}

• Also, it is likely that

{modTitle} is a

candidate key for

Module

•We represent 1:M

relationships as keys

• {stuID} in Enrolment

is a foreign key which

references {stuID} in

Module

•{modCode} in

Enrolment is a foreign

key to {modCode} in

Module

SQL and Java G52DBS – Database System s

www.cs.nott.ac.uk/~smx/DBS

The Program

• We need to

determine

• Progression for first

and second years

• Average marks

• Hard fails

• Soft fails

• Degree classification

for finalists

• Average mark

weighted 40/60 over

2nd and 3rd years

• We will

•Create temporary

tables to hold the

information

• Process each

student’s marks

• Use the temporary

tables to produce the

reports

• Delete the temporary

tables when done

SQL and Java G52DBS – Database System s

www.cs.nott.ac.uk/~smx/DBS

Reports Needed

• For first and second

years (two reports)

• Student name, ID,

average for the year,

number of passes and

hard/soft fails, and a

progression decision

• Ordered by last then

first names

• For finalists

• Student name, ID,

average for second

and third years,

weighted average,

and a degree

classification

• Ordered by average

mark then last then

first names

SQL and Java G52DBS – Database System s

www.cs.nott.ac.uk/~smx/DBS

Basic Design

• We’ll put all the code

in one clas s

• Methods will connect

to and disconnect

from the database,

and generate the

reports

• A member variable is

used to store the

connection to the

database

class ExamReportGen erator {

Connection conn;

connect(…);

produceYear1Repor t(…);

produceYear2Repor t(…);

produceYear3Repor t(…);

disconnect();

public static void main (

String args[]) {

…

}

Partial preview of the text

Download Database Systems: Designing and Generating Reports using SQL and Java and more Slides Introduction to Database Management Systems in PDF only on Docsity!

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

E/R Diagram

Each student is enrolled in a number of modules. The modules each have a title, code, and credit weighting and students have IDs, first and last names. For each enrolment a student has we record the year that the module was taken and the mark achieved. The exam board also needs to know what year a student is currently in

Student

Enrolment

Module

ID

First

Last

Year

Grade

Year

Title

Code

Credits SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

E/R Diagram to Tables

We now make tables

for each entity

Student
Enrolment
Module
The attributes

become columns

1:M relationships are

foreign keys

Student table: stuID, stuFirst, stuLast, stu Year
Module table: modCode, modTitle, modCredits
Enrolment table: stuID, modCode, enrMark, enrYear

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Keys

We declare Primary

keys for each table

Student: {stuID}
Module: {modCode}
Enrolment: {stuID, modCode}
Also, it is likely that {modTitle} is a candidate key for Module

•We represent 1:M

relationships as keys

{stuID} in Enrolment is a foreign key which references {stuID} in Module
{modCode} in Enrolment is a foreign key to {modCode} in Module

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

The Program

We need to

determine

Progression for first and second years - Average marks - Hard fails - Soft fails
Degree classification for finalists - Average mark weighted 40/60 over 2nd and 3rd years - We will - Create temporary tables to hold the information - Process each student’s marks - Use the temporary tables to produce the reports - Delete the temporary tables when done

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Reports Needed

For first and second

years (two reports)

Student name, ID, average for the year, number of passes and hard/soft fails, and a progression decision
Ordered by last then first names - For finalists - Student name, ID, average for second and third years, weighted average, and a degree classification - Ordered by average mark then last then first names

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Basic Design

We’ll put all the code

in one class

Methods will connect to and disconnect from the database, and generate the reports
A member variable is used to store the connection to the database

class ExamReportGenerator { Connection conn; connect(…); produceYear1Report(…); produceYear2Report(…); produceYear3Report(…); disconnect();

public static void main( String args[]) { … } }

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Producing the Reports

Each report will be

written to a file

First and second year reports are very similar, so will use a common method
Temporary tables will be used to store values computed, and then to produce the reports

All reports follow the

same pattern:

Create temp table For each student Compute statistics Store in temp table Produce a report from the temp table Delete temp table

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Progression Reports

For years one and

two we need

progression

information

Based on average mark, number of passes and hard or soft fails
A separate method can be written to work out progression - You progress if: - You pass everything - You pass 100 credits with an average of at least 50 - You pass 80 credits with an average of at least 40, and no hard fails

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

3 rd^ Year Reports

Third year reports

are different

We are computing degree class, not progression
We need to consider the average marks for the student in second and third year

The basic procedure

is the same

Create temp table For each student Compute statistics Store in temp table Produce a report from the temp table Delete temp table

SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Using Java and SQL

SQL is good at

dealing with large

sets of data

Sorting results
Computing sums, averages etc.
Storing or searching large amounts of information
Processing sets of records at the same time - Java is better for

more detailed

processing

Evaluating a small set of information
Working with files
Operations requiring a fair amount of logic or arithmetic
Operations involving complex structures

Database Systems: Designing and Generating Reports using SQL and Java, Slides of Introduction to Database Management Systems

Related documents

Partial preview of the text

Download Database Systems: Designing and Generating Reports using SQL and Java and more Slides Introduction to Database Management Systems in PDF only on Docsity!

E/R Diagram

Student

Enrolment

Module

ID

E/R Diagram to Tables

for each entity

become columns

foreign keys

Keys

keys for each table

•We represent 1:M

relationships as keys

The Program

determine

Reports Needed

years (two reports)

Basic Design

in one class

Producing the Reports

written to a file

All reports follow the

same pattern:

Progression Reports

two we need

progression

information

3 rd^ Year Reports

are different

The basic procedure

is the same

Using Java and SQL

dealing with large

sets of data

more detailed

processing