I hope everyone is well. Last season we covered the front-end structure of web development where we explored how to build our own websites using HTML, CSS and JavaScript. And now in this course we will read about database. And hopefully we will learn and perform well here like last season.

Before I start my task I want to invite some of my friends to participate in this challenge. And learn something about database by coming here and complete your tasks after learning. I invite @rumaisha, @arinaz08, and @neelofar to join this challenge.

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

A column corresponds to:

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

Mark the letter V in front of the correct word to complete the following sentence:
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

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 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

Integrity constraints on a column are:

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

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

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

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

Description of Columns

Anomalies

The table provided to us has various anomalies. Those anomalies relate to data redundancy update anomalies and insertion and deletion anomalies. So that's what I'm explaining below.

Redundancy Anomalies:

The function title and recruitment level are repeated for each employee. Even if it is more than one employee in one function. For example, finance and master appear several times.

Update Anomalies:

If we need to update Finance in the function title, we have to do it for every employee in that function. Because it increases the risk of incompatibility

Insertion Anomalies:

To add a function you will need to re-enter a new employee record. Even if no employee is initially assigned.

Deletion Anomalies:

If any employee assigned to a function is deleted, their function information is also deleted. Thus, if the last employee at a specific recruitment level is terminated, then the information about him is also lost.

Provide a textual representation of this database (with R1 and R2 applied)

We will normalize the given anomalies in different tables according to R1 and R2 rules. After applying R1 and R2 management rules we have below texture representation in different tables.

Table: Employee

codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenom: Employee's first name
demp: Employee's date of birth
niveau: Recruitment level (Foreign Key referencing the Recruitment table)

Table: Function

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

Table: Recruitment

niveau: Recruitment level (Primary Key)

Table: Employee_Function

codemp: Employee code (Foreign Key referencing Employee)
codfct: Function code (Foreign Key referencing Function)
Primary Key: (codemp, codfct)

Textual Representation of the Database (with Updated R1 including Assignment Period):

Table: Employee

codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenom: Employee's first name
demp: Employee's date of birth
niveau: Recruitment level (Foreign Key referencing the Recruitment table)

Table: Function

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

Table: Recruitment

niveau: Recruitment level (Primary Key)

Table: Employee_Function

codemp: Employee code (Foreign Key referencing Employee)
codfct: Function code (Foreign Key referencing Function)
start_date: Start date of the assignment
end_date: End date of the assignment (nullable if the employee is still in the position)
Primary Key: (codemp, codfct, start_date)

Textual Representation of the "Bonus" Table:

Table: Bonus

codemp: Employee code (Foreign Key referencing Employee)
anneePrime: Year of the performance bonus
primeRend: Value of the performance bonus
Primary Key: (codemp, anneePrime, primeRend)

This structure ensures that each employee can receive multiple bonuses in a single year while maintaining accurate records.

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

Create the "GestElections" database

CREATE DATABASE GestElections;
USE GestElections;

Through this code, we will create the database and I have created the database with the same name as mentioned.

Create the various tables in the "GestElections" database.

First of all, I created the Electeur Table:

CREATE TABLE Electeur (
NumElv INT AUTO_INCREMENT PRIMARY KEY,
NomPrenElv VARCHAR(20) NOT NULL,
AgeElv INT CHECK (AgeElv BETWEEN 12 AND 20)
);

The purpose of creating this table is to store the information of the students who will be voting.
NumElv is a primary key that automatically assigns a unique number to each new record.
NomPrenElv: It stores the electors' full name as a text of up to 20 characters.
AgeElv: This will store the age of the electors, which will be between 12 and 20.

Note that the null means that this field holds some value.

Now I created the Candidate Table:

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

This table code is used to store information about candidates running in elections.
NumCand is a unique number that cannot be null.
NumList stores the list number candidates belong to.
Appelationcolumn holds the name and title of candidates.

Created the Liste Table:

CREATE TABLE Liste (
NumList INT NOT NULL PRIMARY KEY,
DesList VARCHAR(20) NOT NULL,
NbrCand INT CHECK (NbrCand BETWEEN 1 AND 6)
);

This code creates a table that stores the information about the list of candidates.
NumListis a unique identifier for each.
The DesList' column stores the unique name and title of the list, which has a maximum length of 20. TheNbrCand` column stores the number of candidates in the list, which must be between 1 and 6.

Created the Voter Table:

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

This code creates the table and records votes. It links candidates, lists, and electors.
NumCand, NumList, and NumElv are unique identifiers for each record to ensure no duplicate votes.
FOREIGN KEY constraints ensure that the values in NumCand, NumList, and NumElv reference valid records in the Candidat, Liste, and Electeur tables, respectively.

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

ALTER TABLE Voter
ADD Datevote DATE DEFAULT CURRENT_DATE;

ALTER TABLE Voter this command modified the ``votertable.ADD Datevote DATE DEFAULT CURRENT_DATEThis adds another segment,Datevote, which stores the date the vote was projected. TheDEFAULT CURRENT_DATE` guarantees that the ongoing date is consequently embedded when another vote is recorded.

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

ALTER TABLE Candidat
MODIFY Appelation VARCHAR(30);

ALTER TABLE Candidat modified the candidate table.
MODIFY AppelationThis order changes the most extreme length of the Appelation section from 20 characters to 30 characters to permit longer competitor assignments.

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

ALTER TABLE Electeur
DROP COLUMN NomPrenElv,
ADD LastName VARCHAR(15) NOT NULL,
ADD FirstName VARCHAR(15) NOT NULL;

ALTER TABLE Electeur This adjusts the "Electeur" table.
DROP COLUMN This eliminates the current NomPrenElv segment, which was utilized to store the complete name of the voter.
ADD LastName This adds another segment LastName to store the voter's last name, with a limit of 15 characters.
ADD FirstName This adds another segment FirstName to store the voter's most memorable name, likewise with a limit of 15 characters. The two fields are obligatory (NO Null).

Proposal for Gender-Based Voting Statistics

To gather measurements about votes in light of orientation, we first need to add a section to the Electeur table to store the orientation of the balloters.

Add Gender Column:

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

ADD Gender This adds another Orientation section to the Electeur table to store the orientation of the balloter.
CHECK This requirement guarantees that main 'M' (male) or 'F' (female) can be placed.
NOT NULLThe Orientation field is required and can't be left vacant.

Example querry Gender-Based Voting Statistics

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

SELECT Gender This inquiry chooses the Orientation of the balloters and counts the number of votes that were projected by every orientation.
JOIN The inquiry joins the Electeur and Citizen tables on the normal field NumElv to relate balloters to their votes.
GROUP These gatherings the outcomes by orientation, permitting us to perceive the number of votes that were projected by male and female balloters.

This is my understanding of this course, hope you guys like it. I have tried my best to complete my task and perform well. Thank you all so much for stopping by.

Cc:
@kouba01

_{Dated: 12-09-2024 About Me}