Description
the 3.1.2, the 3.1.3, the 3.1.4
Unformatted Attachment Preview
Machine Translated by Google
Database Laboratory Project 2023-2024
Contents
1 Introduction ……………………………………….. ………………………………………… ………. 2
2. Description of the Database ………………………………………. …………………….. 3
3. Work Content ………………………………………. …………………………………. 5 3.1. Part
A: NW Design and SQL ……………………………………. ……………….. 5 3.1.2. New
requirements …………………………………………… …………………………….. 5
3.1.3. Create Stored Procedure ………………………………………. …………… 7
3.1.4. Create Trigger ………………………………………… …………………………. 8 3.1.5.
Work structure – Requirements Part I (DB and SQL design) ……….. 9 3.2. Part
B: GUI ………………………………………. ………………………………………… 9 3.2 .1.
Creating Graphical User Interfaces (GUI) …………………………….. 9
3.2.2. Data Validity Check …………………………………………… ………… 9
3.2.3. Optional Required with Bonus Grade …………………………………… 10 3.2. 4.
Work Structure – Requirements Part B (GUI) …………………………………. 10 4.
Submission instructions ……………………………………… ……………………………………….. 10
5. Work in groups – Evaluation ……………………………………. …………………….. 11
6. Examination ………………………………………. ………………………………………… ……….. 12
1
Machine Translated by Google
1 Introduction
The Database Lab project has two goals: • to familiarize
yourself with the design, implementation, input and processing of data in a
relational database (DB), and • to use JDBC technology to
interface the DB with interfaces (Graphical User Interfaces) that you will
implement in Java, in order to gain experience designing a more complete
application.
The project consists of two parts in total: Part
A – NW and SQL Design and Part B – GUI.
Your deliverables will be a report with all the documentation, all the SQL code,
and the code for the GUI. The documentation covers both Part A – NW and SQL
Design and Part B – GUI which are detailed below. In addition, Part B requires
you to explain its operation using screenshots. More detailed deliverables and
deadlines are presented below.
2
Machine Translated by Google
2. Description of the Database The
DB concerns an online personnel evaluation system.
The system is used by businesses that hire external
consultants as Assessors. The Evaluation Officers study
the existing jobs and announce (announce) the positions
to be filled by the company’s employees. Employees
must apply to claim them through appraisal and
promotion. The following functional requirements are
covered by the relational schema given to you by the Preparatory Phase.
• For the users of the system, we keep information about the username (unique
for each user), first name, last name, password, an email, and the date of
registration in the evaluation system. The users of the system are divided
into two categories: • Employees, who are
evaluated in order to be promoted to one of the available jobs and • Evaluators,
who announce the positions they
employees can claim.
•
• For each Employee, the system stores their CV, the languages (English,
French, German, Spanish, Chinese and Greek) they know (one or more),
the name of a file that lists any certifications they have, and the name of a
file with references to discrimination received during his employment with
the company. Each employee may have one or more degrees.
• For each Degree, we record its title, its rank (BSc, MSc, PhD) and the
institution that awards it, the year of acquisition and the degree level. The
combination of title and institution is considered unique. A Degree may be
held by more than one employee, but when a degree is registered, this
implies that an employee must hold it. An employee may or may not hold a
degree.
• In addition, the system records the Projects implemented by the Employees.
Each Project entered in the NW gets an automatic serial number unique to
the Worker who has implemented it. Thus, for the same worker, his first
project entered has a/a 1, the second a/a 2, and so on. Obviously, this
applies to every Employee. For each Project, a description of the
implementation and a url where the code is available are also stored. An
employee may not have participated in a project. • Each Evaluator works
for a company and
his years of experience are recorded.
• For each company with which the Assessors cooperate, the system maintains
the following information: VAT number (unique), the DOU to which the
company belongs, its name, telephone and headquarters (street, number,
city and country). A Company may employ several Assessors.
• Each Job is announced by an Evaluation Manager and its announcement is
accompanied by the start date of the Job, the announcement date and the
deadline for submitting evaluation requests. • Also, for each Job we keep a
code (unique), its title, the
headquarters where the employee will work, and its salary. Furthermore,
3
Machine Translated by Google
each Job requires knowledge of at least one (or more) Items.
• For each Subject we store a (unique) title and description. Each Object can
belong to a more general Object class, just as each Object can contain
one or more, more specific Object classes. The same Item may be
required in more than one Job
• Each Employee may apply for one or more Positions
Work.
4
Machine Translated by Google
3. Work Content 3.1.
Part A: NW and SQL Planning
3.1.1. Preparatory phase: Creating the DB You have
already been given the relational model to create the DB and insert records
into each table. The following number of records per table is the minimum
number of records per member.
Panel
Minimum number of
registrations per person
applies
6
degree
6
employee
6
etaireia
3
evaluator
6
has_degree 4 job
8
languages
4
project
7
requires
8
subject
8
user
12
TABLE 1. MINIMUM NUMBER OF DOCUMENTS PER TABLE AND PER MEMBER
We note that this particular database was used in all the exercises of the Laboratory. In this way and from
the Preparatory Phase familiarity with the shape and data of the base has already been achieved and by
extension a better understanding of the problem you will manage.
3.1.2. New
requirements In this section additional requirements are given regarding
the structure and handling of the DB. Based on the Description (Chapter
2) and considering the new functional requirements listed below, you
should study and modify the DB relational schema to support the new
requirements as well. This process includes (a) extending the relational
schema with additional fields and new tables, (b) inserting an appropriate
number of records, and (c) documenting your design decisions.
3.1.2.1. Requirement: to create a mechanism for the introduction and management of promotion requests. •
Applications should have one of
three statuses: active, completed and cancelled. • New applications for a job can be registered up to 15
days before the position start date (start_date).
5
Machine Translated by Google
• It will not be possible to register a new application or activate a canceled application
for an employee who already has three active applications.
• An employee’s application for a job can be canceled up to 10 days before the
start date of the position (start_date).
3.1.2.2. Requirement: to establish a mechanism for evaluating and deriving results
of promotion applications. • Requests in
canceled status do not participate in the processing. • Each application
will be evaluated by 2 evaluators with a score from 1 to 20, from each one. •
The employee with
the highest average of the two evaluations will get the job. In the event of a tie,
the one who applied earlier is chosen • If, when deriving results for a job,
an employee
application does not have both evaluation points, then one or both missing
points will be filled in depending on the qualifications of the employee and
based on his score per criterion, as shown in the table below.
Qualification Criteria
Grading
BSc degree
1
MSc degree
2
PhD degree
3
For one or more foreign languages 1
For each project that has participated 1
TABLE 2. RATING PER CRITERION
For example, if an employee has a BSc Degree, two MSc Degrees, 2
foreign languages and 3 projects, the recommended grade is ( 1+ 2 X 2+
1+ 3 X 1 =) 7.
• Job applications for which a result has been obtained are deleted from the
table containing the applications to be processed and transferred to the
application history (Requirement 3.1.2.3). Their history status should
change from active or canceled to completed. If the application was
canceled a grade of 0 will be entered, otherwise the grade of the evaluation
will be entered.
3.1.2.3. Requirement: to create application history. You will create a table in which
you will enter the completed applications. For each table entry you will
keep: the usernames of the two evaluators, the usernames of the employee,
the job id, the status (completed) and the grade of the evaluation. You
should enter over 60,000 records in this table. The values in the rating
field will be random integer values from 1-20.
In order to facilitate this process, you can make the design assumption
that the history table does not need to have referential integrity constraints
with other NW tables.
6
Machine Translated by Google
The volume of data is required to address Question 3.1.3.4 only.
3.1.2.4. Requirement: Database Administrators (DBAs) are responsible for the
database, who will be a new user category, respectively, with evaluators
and employees. For Database Administrators (DBAs) the date they
assumed the role (start_date) is respected, which cannot be null. There
is also an end_date date for those DBAs who have left the position.
There may be more than one Database Administrator at the same time
period. The actions of Database Administrators are recorded in a log
table as described in Question 3.1.4.1.
3.1.3. Create Stored Procedure
Create the following stored procedures:
3.1.3.1. Stored procedure in which the username of an evaluator (evaluator), the
username of an employee (employee) and the id of a job (job) are given
as input and checks whether an evaluation degree has been registered
for the evaluator for the specific employee and for the specific job . • If
the evaluator
does not evaluate the specific employee for the specific job, it
returns the value 0 to an output variable.
• If the evaluator evaluates the specific employee for the specific job
position and already has the evaluation grade entered, it returns
this grade to an output variable. • If the evaluator has undertaken
to evaluate the specific employee for the specific job and the
evaluation grade has not been entered, then it is calculated
based on Table 2 of Requirement 3.1.2.2 and returned to an
output variable.
3.1.3.2. Stored procedure in which the username of an employee, the id of a job
and a character (‘i’, ‘c’ or ‘a’) will be given as arguments.
• If the input is ‘i’, then it will create a request for the specific
employee and the specific position, after checking if both
evaluators for the position are filled. If they have not been
completed, they will be completed with evaluators from the same
company.
• If the input is ‘c’, then if there is a request from the specific
employee for the specific position, it will be canceled and an
information message will be displayed. Otherwise it will display
an appropriate message (no request or already
canceled request). • If the input is ‘a’, then if there is a canceled
application from the specific employee for the specific position,
it will be activated and an information message will be displayed.
Otherwise it will display appropriate messages (no request or active request).
7
Machine Translated by Google
3.1.3.3. Stored procedure which takes as an argument the id of a job and will
perform all the necessary actions, as described in Requirement 3.1.2.2,
in order to produce a result for filling the position. If necessary, you can
also use the stored procedure of query 3.1.3.1.
3.1.3.4. Following Requirement 3.1.2.3 and due to the large number of records,
you should create additional indexes on the request history table. For
each of the next two stored procedures you will choose the appropriate
index, add it to the history table and justify your choice.
a) Stored procedure in which two different grades are given as input
parameters (e.g. 7 and 12) and displays the username of the employee
and the id of the job position of all applications evaluated with a grade
in between The space.
b) Stored procedure in which the username of an evaluator is given as
an input parameter and displays the username of the employee and the
job id of all applications evaluated by the specific evaluator.
The purpose of using the indexes is to deal efficiently with the requests
(a) and (b). You will record the times for each run with and without using
an index in the table, and attach an appropriate snapshot. If the data is
not sufficient to show a difference, you will enter more data into the
table.
Hint: See Creating MySQL Indexes and Hashtables (https://dev.mysql.com/
doc/refman/8.0/en/create-index.html)
3.1.4. Create Triggers
Create the following triggers:
3.1.4.1. Triggers that will update the relevant action recording table (log) for each
entry, update or deletion action in the job, user, degree tables with the
date and time and the username of the user who performed it.
3.1.4.2. Trigger that will prevent the entry of an application for a job if the date
the application is entered is less than 15 days from the start date of the
position (start_date). or if that employee already has three active
applications.
3.1.4.3. Trigger which will prevent the cancellation of an application for a job if
the date the application is canceled is less than 10 days from the start
date of the position (start_date). It will also prevent the activation of a
canceled application if the given employee already has three active
applications.
8
Machine Translated by Google
3.1.5. Work Structure – Requirements Part I (DB Design and SQL ) You
will deliver a report with all documentation and all SQL code. Your report
will include Names, MA and years of study for all team members. Your
report will then be developed into chapters based on the requests. In
detail, the requirements for Part A are as follows:
• In your Chapter 1 report you will include the relational diagram of the overall
revised BD (after the extensions mentioned above). You will also include a
description of all your design assumptions, the new tables and modifications
you made to existing tables, and annotating the new tables you created to
enable you to implement what was requested. Finally, it will include the sql
commands for creating the DB (create, insert, update, alter).
• In Chapter 2 of your report you will provide the code and
examples from the execution of the stored procedures of Section 3.1.3.
• In Chapter 3 of your report you will provide the code and examples
(screenshots) from the execution of the triggers of Section 3.1.4.
Instructions for submission can be found in Chapter 4.
3.2. Part B: GUI
The DB will be used by Database Administrators (DBAs). In order to
cover the following needs it will be necessary to create an interface/
fes. Note that the implementation of these interfaces may require
some revisions to the design (eg, adding some fields to tables).
3.2.1. Creating Graphical User Interfaces (GUIs)
Develop in Java using an IDE of your choice (NetBeans, etc.) the following
functionality: The system should provide a GUI from which to select any DB
table. After selecting the table, its contents will be displayed in the interface and
the user will be able to insert, modify and delete data (in the given table).
3.2.2. Data Validation Check Design
your application in such a way that, during the input or modification
process mentioned above (3.2.1) and where possible, the input of data by
the user is limited, in order to avoid errors and processes are accelerated
(e.g. selecting from menus or lists). The options that will be available will
come from the data that exists in the NW. For example, when adding
evaluators to a job, display a list of registered evaluators.
9
Machine Translated by Google
3.2.3. Optional Bonus Grade Requested Add
functionality (eg, when issuing results to show a total of applications that
were processed and which workers got the positions) or additional
interfaces (eg, a new interface that shows all data of an evaluator and the
positions he evaluates).
3.2.4. Work Structure – Requirements Part B (GUI)
• In Chapter 4 of your report, you will describe the documentation and
Java code that you developed in Part B. You will also describe a
usage scenario, and provide screenshots that demonstrate the
functionality of your application.
• Finally, if you do 3.2.3, in chapter 5 describe and document the functionality
you added. Also provide screenshots showing the additional functionality.
4. Submission instructions
The submission of the project will be done by Monday 22/01/2024, 23:55 and
must be submitted in TWO assignments in eclass with titles:
• 2023-2024 Project Submit Documentation DOC or PDF ONLY •
2023-2024 Project Submit ALL ZIP files
CAUTION! Regardless of whether you did only Part A or both (A and B) you
MUST submit BOTH papers in eclass.
Deliverables per task in eclass:
I) In the Submit Documentation task ONLY DOC or PDF
Submit a .doc. or .pdf file which will have all the documentation and all the SQL
code and will pass a similarity check. The documentation covers both part A
and part B (if you have implemented it).
The structure of the report in chapters is described in Sections 3.1.5 and 3.2.4.
The insert statements for the 60,000 records do not need to be inserted into the report.
II) In the Submit ALL ZIP files task
Submit a .zip file that includes
• the previous one (.doc. or .pdf file which will have all the documentation
and all the SQL code), • all the code for Parts
A and B (if you have implemented it) and • all the files that are required to
demonstrate the code.
The submission is made by the same team member in BOTH assignments in
eclass. The name of each submission file (.doc-.pdf and .zip) will consist
• from the names and IDs of all members of the group and the letter A
• additionally the letter B if you have implemented AND GUI.
10
Machine Translated by Google
For example:
Group without Part B
submission of files:
Papadopoulos_10101010_Xristopoulos_10101011_A.pdf and
Papadopoulos_10101010_Xristopoulos_10101011_A.zip in both papers
Group with Part B
submission of files:
Dimitropoulos13131313_Papaioannou12121212_AB.pdf and
Dimitropoulos13131313_Papaioannou12121212_AB.zip in both papers
5. Working in groups – Assessment The project is designed
for you to work in groups of 3 people. If you wish, you can work in groups of 2 with variation in
the units of each subject compared to a group of 3. The variation exists because of the amount
of work for a 2 person team. In addition, for a group of 2 people, there is a bonus if the question
3.2.2 is implemented, while for a group of 3 people the specific question (3.2.2) is mandatory
and without bonus. Below is analyzed the differentiation of the score in the two cases of teams:
Total
3.2.3
(bonus)
Group 3.1.1 3.1.2 3.1.3 3.1.4 3.2.1 3.2.2
3 people 1 2 people
1
4
1.5
2
0.5
0.5
7.5+2.5+0.5=10.5
1.5
1
4
2
1.5
1
1
8.5+2.5+1=11.5
(bonus)
• A group of 3 people needs to answer all the questions correctly (3.1.1 to
and 3.2.2) to achieve a score of 10.
• A team of 2 people needs to answer all questions from 3.1.1 to 3.2.1 correctly to get a
score of 10, while if they answer questions from 3.1.1 to 3.2.2 correctly they get a score
of 10.5. • Additional 3.2.3 functionality is rated up to bonus.
The project is mandatory and accounts for 50% of your final grade in the Database Lab. The
other 50% results from Laboratory Exercises 1 to 5. It is not necessary to have a grade greater
than 5 in all of the above. It is sufficient that the overall grade of the laboratory is transferable,
as described in the original instructions of the Laboratory. Therefore, if your project score is
greater than 10 it significantly improves the final Lab grade, especially if some of your exercises
have a low score since it counts with them.
11
Machine Translated by Google
6.
Examination The oral examination will take place during the
examination on a date to be announced. The oral exam is
MANDATORY for all team members. After the end of the oral
exam, the final project grade will be calculated for each team member individually.
During the oral exam, each team will demonstrate the project and its operation on
ONE computer of their own, either remotely or up close. If any member of the team
does not attend the exam, they will be graded zero and will not pass the Workshop.
During the exam, the team should have prepared to demonstrate each question
using a suitable demonstration scenario and then grading will be done based on the
above grading table. Each examinee will be quizzed on the code demonstrated by
the team. The grade of each team member may vary, depending on their oral exam.
Assessment requires presentation of working SQL (and java for those
doing Part B) code. Code that does not work is not reviewed and not rated!
A relevant announcement will be made regarding the oral examination process, after
the submission of the projects.
12
Purchase answer to see full
attachment
