I tried the Databases and SQL part 1 tasks!

Hope I got some right answers 😥

---

Exercise 1:

V= correct, F = false

• 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 f
  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 v ?
  ON UPDATE CASCADE f
  ON DELETE CASCADE f

• Integrity constraints on a column are:
  PRIMARY KEY v
  CHECK f
  FOREIGN KEY v
  REFERENCES f

• 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

---

Exercise 2:

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

Exercise table made in Google spreadsheet by me

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

In the intfct column, there's a lot of repeated function titles, this could be at risk of human error when needing to update the title. Like, if you had to change the title from 'Security' to 'Security officer', you'd have to manually update every single one. Which can easily result in a missed change if there's thousands of employees.

From the repeated function titles in the intfct column, another issue is how much space each repeated title takes. Instead, this job title could be stored in a separate table and then just referenced.

• Question: Provide a textual representation of this database.

So I wrote this while taking into account the anomalies from the previous question, and the requirement of:

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

Function (codfct, intfct)

Employee (codemp, nomemp, prenemp, dnemp, niveau)
_{In the Employee table, niveau is a foreign key. I've put it as a link for niveau_table.}

Employee_codfct (codemp, codfct, start_date, end_date)
_{here, I tried to do more with foreign keys, codemp and codfct. With the aim of recording the start and end date of assignments}

niveau_table (niveau, description)
_{I wanted to use description for the levels (High school, Technician)}

I got a bit confused with how to answer this.

---

• Question: Provide the textual representation of this database.

We propose to add a table named "bonus"... fields AnneePrime as the year of the bonus and PrimeRend as the value of the bonus. ... respect... R3

employee (codemp, nomemp, prenemp, dnemp, niveau)
_{niveau is a foreign key here}

Function (codfct, intfct)

Employee_codfct (codemp, codfct, start_date, end_date)
_{foreign keys: codemp and codfct}

niveau_table (niveau, description)

bonus (codemp, AnneePrime, PrimeRend, combo)
_{foreign key: codemp. combo is a combination of codemp, AnneePrime, and PrimeRend. combo is needed because there can be multiple bonuses for the same employee each year, so it needs to be recorded as unique each time, avoiding any duplication.}

---

Exercise 3:


CREATE DATABASE GestElections;



CREATE TABLE Electeur (
NumElv INT AUTO_INCEMENT PRIMARY KEY CHECK (NumElv BETWEEN 1 AND 999),
NomPrenElv VARCHAR(20) NOT NULL,
AgeElv TINYINT UNSIGNED CHECK (AgeElv BETWEEN 12 AND 20)
);



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



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



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


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


ALTER TABLE Voter
ADD Datevote DATE DEFAULT CURRENT_DATE;


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


ALTER TABLE Candidat
MODIFY Appelation VARCHAR(30);


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


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


---

• Head of the high school: What can be proposed to him? Write your proposal in SQL.

So, because the compilation of votes is according to gender, I need to add this as an option to my database.


ALTER TABLE Electeur
ADD Gender ENUM('Boy', 'Girl') NOT NULL;


Not really sure how then to compile the statistics 😅

Inviting 3 friends!

@alejos7ven
@artskills
@flowertron

Twitter

Unfortunately I don't use Twitter...

Haa, this was a bit hard, took me a long time! Hope it's decent, I don't have much experience in this to be honest. I did watch quite a few youtube videos as I wasn't sure what to do for some questions.