SEC S20W1 || Databases and SQL language - Part 1

Dear Professor,

This is my submission for the Engagement Challenge. I am impressed with this time homework contest relate with Databases and SQL Language. I want to Thank you to professor @kouba01 to organizing such a valuable contest.

Image Source

Exercise 1

For each of the following statements, validate each proposition by putting the letter V if it is correct or F if it is incorrect.

Q1) A column corresponds to:

• an index: F
• an elementary property of a real-world
object: V
• a real-world object: F

Instead of being a simple property, a column in MySQL usually reflects an attribute of an actual object, such its name or age.

Mark the letter V in front of the correct word to complete the following sentence:

Q2) A ……….. is a set of data related to the same subject (or entity) and structured in a table format.

• Database: F
• Table: V
• Row: F

A table makes comparison and analysis easier by arranging data into rows and columns and clearly showing relevant information.

Q3) Among the following propositions, which correspond to a primary key?

• A field that does not accept duplicates: V
• Allows identifying information to retrieve
it: V
• First record in a table: F

In database parlance, this type of field is called a "primary key" or "unique identifier". It guarantees that every record is unique and that it may be effectively accessed or retrieved by utilizing its unique value.

Q4) In SQL, to ensure data integrity in a child table after modifying the primary key value of a parent table, we use the clause:

• CHECK: F
• ON UPDATE CASCADE: V
• ON DELETE CASCADE: F

By automatically updating foreign key values in the child table to correspond with modifications in the parent table's primary key and maintaining referential consistency, the ON UPDATE CASCADE clause guarantees data integrity.

Q5) Integrity constraints on a column are:

• PRIMARY KEY: V
• CHECK: V
• FOREIGN KEY: V
• REFERENCES: V

Data accuracy and consistency are guaranteed by integrity constraints such as PRIMARY KEY, CHECK, FOREIGN KEY, and REFERENCES. The primary key ensures unique identity, the foreign key preserves relational integrity, the referential linkages between tables are enforced by REFERENCES, and values are validated by CHECK.

Q6) In SQL, which clauses placed after the data type of a field ensure that the content of this field is unique?

• PRIMARY KEY: V
• UNIQUE: V
• NOT NULL: F
• CONSTRAINT: F

Both UNIQUE and PRIMARY KEY guarantee uniqueness; UNIQUE permits more than one null value, whereas PRIMARY KEY only permits unique, non-null values.

Q7) In SQL, the Drop Table command allows:

• Deleting integrity constraints on a table
only: F
• Deleting a table: V
• Deleting one or more rows from a table:
F
• Deleting one or more columns from a table: F

A table and its contents are permanently removed from the database by using the DROP TABLE command, which also frees up resources and space.

---

Exercise 2:

To describe the employees of a company and their distribution among different functions, the following table has been created:

codemp	nomemp	prenemp	dnemp	niveau	codfct	intfct
E001	TOUNSI	Safa	1980-01-10	Master	20	Finance
E002	KEFI	Ali	1981-09-12	High School	10	Administrative
E003	BEJI	Mohamed	1977-04-15	High School	20	Finance
E004	TOUATI	Lamia	1965-06-21	Technician	20	Security
E005	SOUSSI	Leila	1982-11-28	Master	10	Administrative
E006	SFAXI	Lotfi	1988-06-09	Master	30	Legal

Description of Columns

Column	Description	Column	Description
codemp	Employee code	dnemp	Employee's date of birth
nomemp	Employee's last name	niveau	Recruitment level
prenemp	Employee's first name	codfct	Function code
intfct	Function title

• This table suffers from anomalies from its creation. Identify the anomalies in this structure and explain their origins.

Identifying Redundancy and Update Anomalies: The present table structure contains both function-related information (function code and title) and recruitment-level information, which may result in redundancy and update anomalies.For example, if a function title changes, it must be changed in all records where that function appears.

Insertion Errors: Adding a new function or recruitment level necessitates inserting numerous records. For example, if a new function is added, you must insert numerous entries, which may result in inconsistency if the function details are not updated consistently.

To correct the anomalies, we propose the following management rules:

R1: An employee is assigned to only one function, and a function can include several employees.
R2: An employee has only one recruitment level, and a recruitment level concerns several employees.

Question: Provide a textual representation of this database.

For the DBMS to take into account that an employee can occupy a position in the company for a determined period, and may occupy the same position at different times,

R1 becomes: An employee can hold one or more functions, and for each assignment, the start and end dates of the assignment are recorded.

Question: Provide the textual representation of this database.

We propose to add a table named "bonus" to record performance bonuses awarded to each employee during a year, with the fields AnneePrime as the year of the bonus and PrimeRend as the value of the bonus. To do this, we must respect the new management rule R3: An employee can receive performance bonuses during the same year.

Question: Provide the textual representation for this table.

Here is my answer for the Exercise 2,

Identifying Anomalies

Redundancy and Update Anomalies:

Function Information: For every employee designated to a certain function, the intfct (Function title) is repeated. There may be discrepancies if a function's title changes because all records containing that function will need to be changed. Information about the Recruitment Level: Each employee receives the same niveau (Recruitment level), which could lead to redundancy if the description of the Recruitment Level changes.

Insertion Anomalies:
It may be inefficient and error-prone to ensure that all relevant personnel records are updated when new functions or recruitment levels are added.

Anomalies of Deletion: If there is no other record connecting the removal of a function or recruitment level to its specifics, information may be lost. Modified Database Structure First Textual Illustration:

Corrected Database Structure

Initial Textual Representation:

Employee Table:

codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenemp: Employee's first name
dnemp: Employee's date of birth

Function Table:

codfct: Function code (Primary Key)
intfct: Function title

Recruitment Level Table:

niveau: Recruitment level (Primary Key)

Employee Function Assignment Table:

codemp: Employee code (Foreign Key from Employee Table)
codfct: Function code (Foreign Key from Function Table)
start_date: Start date of the function assignment
end_date: End date of the function assignment (can be NULL if currently assigned)
Primary Key: (codemp, codfct, start_date)
With New Requirements for Multiple

Assignments and Bonuses

Employee Table:

codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenemp: Employee's first name
dnemp: Employee's date of birth
Function Table:

codfct: Function code (Primary Key)
intfct: Function title

Recruitment Level Table:

niveau: Recruitment level (Primary Key)

Employee Function Assignment Table:

codemp: Employee code (Foreign Key from Employee Table)
codfct*: Function code (Foreign Key from Function Table)
*start_date: Start date of the function assignment
end_date: End date of the function assignment (can be NULL if currently assigned)
Primary Key: (codemp, codfct, start_date)

Bonus Table:

codemp: Employee code (Foreign Key from Employee Table)
AnneePrime: Year of the bonus
PrimeRend: Value of the bonus
Primary Key: (codemp, AnneePrime)

This format eliminates redundancy, sets off employee data from function and recruiting level specifics, and facilitates the effective tracking of numerous assignments and performance bonuses.

---

Exercise 3

Let’s consider the database "GestElections," which contains information about the elections of the foundation council members of a high school. It is described by the following simplified textual representation:

| Electeur (NumElv, NomPrenElv, AgeElv) |
| Candidat (NumCand, NumList#, Appelation) |
| Liste (NumList, DesList, NbrCand) |
| Voter (NumCand#, NumList#, NumElv#) |

Description of the columns in the tables

Column	Description	Data Type	Size	Mandatory	Table
NumElv	Number of an elector student	Auto-increment	3	Yes	Electeur
NomPrenElv	Elector student's full name	Text	20	Yes	Electeur
AgeElv	Elector student’s age [12..20]	Numeric	2	No	Electeur
NumCand	Number of a candidate student	Numeric	2	Yes	Candidat
NumList	List number	Numeric	1	Yes	Liste
Appelation	Candidate's designation	Text	20	Yes	Candidat
DesList	Unique designation of a list	Text	20	Yes	Liste
NbrCand	Number of candidates [1..6]	Numeric	2	No	Liste

The database administrator asks you to help create and modify the structure of the tables indicated above.
Therefore, provide the SQL commands to:

• Create the "GestElections" database:

• Create the various tables in the "GestElections" database.

• Add a "Datevote" column representing the current date to the "Voter" table.

• Expand the "Appelation" column of the "Candidat" table to 30 characters.

• Split the "NomPrenElv" column of the "Electeur" table into a "LastName" and "FirstName," each of 15 characters (both fields are mandatory).

The head of the high school wants to compile statistics on votes according to the gender of the elector (boy or girl) but cannot find results.

What can be proposed to him?
Write your proposal in SQL.

Here's a list of SQL commands to complete the requested tasks:

1. Create the "GestElections" Database
   sql code:-
   CREATE DATABASE GestElections;

^{Screenshot was taken by me.}

1. Create the Various Tables in the "GestElections" Database
   sql code:-
   USE GestElections;

CREATE TABLE Electeur (
NumElv INT AUTO_INCREMENT PRIMARY KEY,
LastName VARCHAR(15) NOT NULL,
FirstName VARCHAR(15) NOT NULL,
AgeElv INT
);

CREATE TABLE Candidat (
NumCand INT PRIMARY KEY,
NumList INT NOT NULL,
Appelation VARCHAR(20) NOT NULL,
FOREIGN KEY (NumList) REFERENCES Liste(NumList)
);

CREATE TABLE Liste (
NumList INT PRIMARY KEY,
DesList VARCHAR(20) NOT NULL,
NbrCand INT
);

CREATE TABLE Voter (
NumCand INT,
NumList INT,
NumElv INT,
Datevote DATE,
PRIMARY KEY (NumCand, NumList, NumElv),
FOREIGN KEY (NumCand) REFERENCES Candidat(NumCand),
FOREIGN KEY (NumList) REFERENCES Liste(NumList),
FOREIGN KEY (NumElv) REFERENCES Electeur(NumElv)
);

1. For the "Voter" table, add a "Datevote" column. (This phase and the Datevote column definition are part of the CREATE TABLE Voter command.)

1. Increase the length of the "Candidat" table's "Appelation" column to 30 characters.

SQL code:-

ALTER TABLE Candidat
MODIFY Appelation VARCHAR(30) NOT NULL;

1. Divide the "NomPrenElv" Column into "LastName" and "FirstName" in the "Electeur" Table.

SQL code:-
Assuming you have a copy of the data on hand or that dropping and rebuilding the table is OK.Divide the "NomPrenElv" Column into "LastName" and "FirstName" in the "Electeur" Table.

-- Drop existing table
DROP TABLE Electeur;

-- Recreate table with split columns
CREATE TABLE Electeur (
NumElv INT AUTO_INCREMENT PRIMARY KEY,
LastName VARCHAR(15) NOT NULL,
FirstName VARCHAR(15) NOT NULL,
AgeElv INT
);

--If you have a technique for splitting names, you can replicate old data into the new form. Proposal for Gender-based Statistics To collect information based on the gender of the elector (boy or girl), first add a gender column to the Electeur table. Here's how to change the schema and input sample data to allow gender-based queries:

Add Gender Column to Electeur Table:
sql code

ALTER TABLE Electeur
ADD Gender CHAR(1) CHECK (Gender IN ('M', 'F')) NOT NULL;

Update the Electeur Table with Gender Data:
sql code:-
-- Example: Update gender data for existing records
UPDATE Electeur
SET Gender = 'M'
WHERE LastName = 'Smith' AND FirstName = 'John'; -- Use appropriate conditions

Compile Statistics Based on Gender:
sql code:-

-- Count the number of voters by gender

*SELECT Gender, COUNT() AS **
NumberOfVoters
FROM Electeur
JOIN Voter ON Electeur.NumElv = Voter.NumElv
GROUP BY Gender;

By adding a gender column and adjusting the queries, you can easily compile statistics and analyze voting patterns based on gender.

^{Screenshot was taken from my laptop screen}

I would like to invite @shiftitamanna, @ripon0630 and @kyrie1234 to take part in this wonderful contest.

Thank you for giving a time!