SEC S20W1 || Databases and SQL language - Part 1

_{Created by @kouba01 using Canva & PhotoFilter}

Dear Steemians,

We are very pleased to announce the official launch of the "SEC S20W1: Databases and SQL Language - Part 1" competition organized by our Dynamic Devs team. This competition is a golden opportunity to showcase your skills in SQL and database management, two essential tools in the digital world.

We warmly invite you to participate and show us your talents. This challenge is not only technical, but it will also allow you to share your ideas and knowledge with an engaged community. The best publications will be rewarded and honored!

Whether you are a database enthusiast or simply curious to learn more about SQL, we are expecting many of you. Get ready to take up the challenge and show your mastery of this powerful tool.

Don't forget to invite your friends and colleagues to join us in this intellectual and enriching adventure!

Good luck to all and may the best articles win the honors!

A- Structure of a Relational Database

I. Table Concept:

A table is a data set related to a single entity, structured as a table (list). A table can also be called a "Relation". A table consists of:

• Columns (attributes) indicating different attributes.
• Rows containing the values of these attributes.

The set of values in a row is called a record/tuple.

II. Column Concept:

1. Definition:
   A column (field) represents a fundamental property of the entity described by this table.

2. Characteristics of a Field:
   a. Name
   b. Data type
   c. Possible size
   d. Mandatory (yes/no) Not Null
   e. Default value
   f. Valid if: A rule can be created indicating the permissible values.

• Avoid column redundancy within the same table. Represent information in its basic form, meaning it should not be decomposed to be manipulated. Example: A student's name should be initially broken down into (first name and last name).

• A column should not represent information that can be calculated or derived from other columns. Example: The "Final_Score" column should not exist if it is a result of multiplying the "score/20" and "coefficient" columns.

• Similar columns should not be present; only one column should be retained. Example: The columns "Score1", "Score2", "Score3" should be replaced by the column "score".

• Omitting a column results in a lack of information.

III. Row Concept:

• A row (record/tuple) represents an occurrence of an object represented by the table. Example: A row in the "book" table:

IV. Primary Key Concept:

a) In the following tables, check the box that allows for unique identification of the entity.

b) Deduce the importance of the checked field.

Important remarks:

• Each table must have one and only one primary key.
• A primary key can be simple (represented by one column) or composite (made up of several columns).
• The columns that make up the primary key are mandatory.
• If none of the table's columns can be considered a primary key, a new column should be added and used as the primary key.
• To represent a column that is part of the primary key in other columns, it should be underlined.

V. Relationships Between Tables:

In a relational context, entities in an information system have relationships with each other. These relationships can be expressed as follows:

One-to-One Relationship:
This relationship is straightforward and does not require representation. It involves grouping the columns of related tables into a single table.
Example: A person has only one ID card. An ID card is related to only one person.
In this case, the "person" table should group all the columns from both tables (person and ID card):
Person(cin, name, first name, phone, father_first_name, mother_full_name, issuance_date, fingerprint_id)

One-to-Many Relationship:
Case of the "Student" and "Class" tables:
A student belongs to one class, and one class contains many students.

In this case, "CLASS" is the parent table, and "STUDENT" is the child table. Since "CLASS" is the parent, its primary key migrates to the child table ("STUDENT") and becomes a foreign key.
This is referred to as a one-to-many relationship (1, ∞).
The primary key of a table is a field or a set of fields that uniquely identifies each record in the table. A primary key must be unique and not null.

Many-to-Many Relationship:
Case of the "Athletes" and "Races" tables:
An athlete can participate in several races, and a race can involve many athletes.

In this case, the "participate" relationship is transformed into a table identified by two columns, which are the primary keys of the "athlete" and "race" tables (the two keys will be both primary and foreign keys in the new table).

VI. Database Structure Representation:

Textual Representation:

Example:
Institution (CodeInst, NameInst, AddressInst, PhoneInst)
Student (StudentID, LastName, FirstName, BirthDate, CodeInst#)

Graphical Representation:

VII. Integrity Constraint Concept:

An integrity constraint is a rule applied to a field or a table that must always be met.

1. Domain Constraints: Applied to columns (valid if).
   Example: 0 <= Grade <= 20, StockQty >= 0

2. Table Integrity Constraints: Each table must have a primary key.
   Example: The "student" table must have a primary key (StudentID).

3. Referential Integrity Constraints (foreign key):
   The foreign key field can only contain a value already existing in the corresponding primary key.
   Example: You cannot assign the value C4 to the "ClassNumber" field in the "student" table if class C4 does not exist in the "class" table.

   The deletion of a record from a parent table A that is used by a child table B is prohibited.
   Example: You cannot delete a record from the "class" table if it has occurrences in the "student" table.

B- Creation and Modification of Database Structure

I) Introduction:
This consists of creating and modifying the different structures of a database using SQL (Structured Query Language). This language is composed of three command families:

• DDL: Data Definition Language
• DML: Data Manipulation Language
• DCL: Data Control Language

Note: We are only interested this week in DDL

II) Creating a Database in Command Mode:

Let’s consider the textual representation of the following database “gestion_commande”:

• Article (Code_art, des_art, PU, qte_stock)
• Client (Code_cl, nom_cl, Pren_cl, adr_cl, tel_cl, dn, age)
• Commande (Num_com, date_com, code_client#)
• Detail_commande (num_com#, code_art#, qte_com)

Description of Columns:

Here is the table without HTML tags:

Column Name	Data Type	Details
Code_cl	Integer	Auto-incremented
nom_cl, pren_cl	String	Required, up to 15 characters
adr_cl	String	Up to 60 characters
Tel_cl	Integer	8 digits
dn	Date
age	Integer	Between 5 and 99
Code_art	String	Up to 5 characters
des_art	String	Up to 50 characters, must be unique
PU	Positive real number	8 digits with 3 decimal places
qte_stock	Positive integer	5 digits, default value 0
Num_com	Integer	Auto-incremented
date_com	Date	Default value is today’s date
qte_com	Integer

a) Creating a Database:
CREATE DATABASE database_name;

Q1: Write the SQL command to create the "gestion_commandes" database.

• CREATE DATABASE gestion_commandes;

b) Creating a Table:

CREATE TABLE table_name (
  column_name1 Type [[NOT] NULL] [DEFAULT value] [constraint_column1],
  column_name2 Type [[NOT] NULL] [DEFAULT value] [constraint_column2],
  ...
);

Notes:

• The table name must be unique within the database and should not be a reserved keyword.
• The words in square brackets [] are optional.
• NULL: Indicates that the column is optional.
• NOT NULL: Indicates that the column is required.
• DEFAULT value: This option should not be used when the column is required (NOT NULL).

b.1) Data Types:

• INT(n): Numeric with n digits.
• DECIMAL(n, m): Real number with n digits (including the decimal point) and m digits after the decimal point.
• VARCHAR(n): String of variable length with a maximum size of n (limited to 255 characters).
• Date: Date.
• TIME: Time.
• CHAR(n): Fixed-length string of n characters (limited to 255 characters).
• TEXT: String of length exceeding 255 characters; no default value for TEXT type.

b.2) Integrity Constraints:

• Column Constraint:
  The [constraint_column] option is used to control the validity and coherence of the values in a column.

General syntax:

[CONSTRAINT constraint_name]
{PRIMARY KEY
|REFERENCES table_name [(column_name)] [ON DELETE CASCADE] [ON UPDATE CASCADE]
|CHECK(condition)}

• PRIMARY KEY: Specifies that the column is used as the primary key.
• REFERENCES: Defines a referential integrity constraint, indicating that the column is a foreign key. table_name is the parent table, and column_name is the primary key of the parent table. It is used when the primary key in the parent table has a different name from the foreign key.
• ON DELETE CASCADE: Automatically deletes records in the child table that have foreign key values dependent on a primary key if that primary key is deleted from the parent table.
• ON UPDATE CASCADE: Automatically updates the foreign key values in the child table when the corresponding primary key in the parent table is modified.
• CHECK: This keyword is associated with a condition that must be validated for each inserted value.

Example:

• CHECK (Price > 0);
• CHECK (qte_stock >= 0);
• CHECK (sexenf = 'M' OR sexenf = 'F');
• CHECK (NoteC BETWEEN 0 AND 20);

Q2: Create the "CLIENT" and "ARTICLE" tables of this database:

CREATE TABLE Client (
  Code_cl INT auto_increment PRIMARY KEY,
  nom_cl VARCHAR(15) NOT NULL,
  pren_cl VARCHAR(15) NOT NULL,
  adr_cl VARCHAR(60),
  tel_cl INT(8),
  dn DATE,
  age INT(2) CHECK (age BETWEEN 5 AND 99)
);

CREATE TABLE Article (
  Code_art VARCHAR(5) PRIMARY KEY,
  des_art VARCHAR(50) UNIQUE,
  PU DECIMAL(8,3) CHECK (PU > 0),
  qte_stock INT(5) DEFAULT 0 CHECK (PU >= 0)
);

Table Constraint:

This is a constraint applied to multiple columns.

General Syntax:

[CONSTRAINT constraint_name]
{PRIMARY KEY} (column1, column2, ...)
|FOREIGN KEY (column1, column2, ...) REFERENCES table_name [(column1, column2, ...)]
|[ON DELETE CASCADE] [ON UPDATE CASCADE]
|CHECK (condition)}

• PRIMARY KEY: Specifies that column1, column2 are used as the primary key.
• FOREIGN KEY: Defines a referential integrity constraint related to one or more columns. The table name specified after the word REFERENCES is the parent table. The column names are those to which the foreign key refers, and they should be enclosed in parentheses.

---

Q3: Create the tables "COMMANDE" and "DETAIL_COMMANDE" for this database:

CREATE TABLE Commande (
  Num_com INT auto_increment PRIMARY KEY,
  Date_com DATE DEFAULT NOW(),
  Code_client INT,
  CONSTRAINT fk_cl FOREIGN KEY (Code_client)
  REFERENCES Client (Code_cl)
);

CREATE TABLE Detail_commande (
  Num_com INT NOT NULL,
  Code_art VARCHAR(5) NOT NULL,
  Qte_com INT,
  CONSTRAINT pk_com PRIMARY KEY (Num_com, Code_art),
  CONSTRAINT fk1_com FOREIGN KEY (Num_com)
  REFERENCES Commande (Num_com),
  CONSTRAINT fk2_com FOREIGN KEY (Code_art)
  REFERENCES Article (Code_art)
);

Note 1:
If a table has a composite primary key (composed of multiple columns), it is impossible to specify PRIMARY KEY at the column level.

Note 2:
Consider the following textual representation related to the management of room reservations in a hotel:

• ROOMS (NumCh, NumHot#, Nbr_lit)
• RESERVATIONS (NumCin#, NumCh#, NumHot#, StartDate, EndDate, Num_pers)
• CLIENTS (NumCin, ClientName, ClientSurname, ClientProfession, ClientAddress, ClientPhone)

It may happen that a primary key, composed of several columns, is also a foreign key in another table. In this case, the composite foreign key is indicated after the definition of the columns using the keyword FOREIGN KEY, followed by the foreign key columns and REFERENCES, the name of the referenced table, and the names of the referenced columns enclosed in parentheses.

III) Modifying the Structure of a Database:

It is possible to modify the structure of a table at any time by using the following SQL commands:

ALTER TABLE table_name
    [ADD COLUMN column_definition]
    [ADD CONSTRAINT constraint_definition]
    [MODIFY|ALTER column_definition]
    [CHANGE column_name1 column_name2 column_type]
    [RENAME column_name1 TO column_name2]
    [DROP COLUMN column_name]
    [DROP CONSTRAINT constraint_name]
    [ENABLE|DISABLE constraint_name];

Command Roles and Examples:

1. Adding a New Column:

ALTER TABLE table_name
ADD [COLUMN] column_definition;

Example:
Adding a mandatory Email column (80 characters) to the "client" table:

ALTER TABLE Client
ADD Email VARCHAR(80) NOT NULL;

2. Modifying an Existing Column:

ALTER TABLE table_name
MODIFY [COLUMN] new_column_definition;

ALTER TABLE table_name
ALTER [COLUMN] new_column_definition;

Example:
Increasing the length of the Adr_cl column by 20 characters:

ALTER TABLE Client
MODIFY Adr_cl VARCHAR(80);

Making the des_art column in the "article" table mandatory:

ALTER TABLE Article
MODIFY des_art VARCHAR(50) NOT NULL;

3. Renaming a Column:
Renaming a column is not functional in MySQL. However, you can use the CHANGE syntax.

ALTER TABLE table_name
CHANGE [COLUMN] old_column_name new_column_name column_type;

Example:
Renaming the code_client column in the "commande" table to code_cl:

ALTER TABLE Commande
CHANGE code_client code_cl INT;

4. Deleting a Column:

ALTER TABLE table_name
DROP [COLUMN] column_name;

Example:
Deleting the age column from the "client" table:

ALTER TABLE Client
DROP age;

5. Adding a New Constraint:

ALTER TABLE table_name
ADD CONSTRAINT [constraint_name] constraint_definition;

Example:
Adding a constraint named ct1 to ensure Qte_com is greater than 0:

ALTER TABLE Detail_commande
ADD CONSTRAINT CT1 CHECK (Qte_com > 0);

6. Disabling a Constraint:
If the integrity constraint concerns the primary key, foreign keys referencing the table must be removed before disabling the primary key.

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example:
Disabling the domain integrity constraint ct1:

ALTER TABLE Detail_commande
DISABLE CONSTRAINT CT1;

7. Enabling a Constraint:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example:
Enabling the domain integrity constraint ct1:

ALTER TABLE Detail_commande
ENABLE CONSTRAINT CT1;

8. Deleting a Constraint:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example:
Deleting the domain integrity constraint ct1:

ALTER TABLE Detail_commande
DROP CONSTRAINT CT1;

Note: ENABLE and DISABLE are not functional with MySQL.

---

d) Deleting a Table:

DROP TABLE table_name;

Example:

DROP TABLE Client;

e) Deleting a Database:

DROP DATABASE database_name;

Example:

DROP DATABASE gestion_commandes;

---

Homework :

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.

A column corresponds to:

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

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
• Table
• Row

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

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

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

Integrity constraints on a column are:

• PRIMARY KEY
• CHECK
• FOREIGN KEY
• REFERENCES

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

• PRIMARY KEY
• UNIQUE
• NOT NULL
• CONSTRAINT

In SQL, the Drop Table command allows:

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

---

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.

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.

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.

Contest Guidelines

Post can be written in any community or in your own blog.

Post must be #steemexclusive.

Use the following title: SEC S20W1 || Databases and SQL language - Part 1

Participants must be verified and active users on the platform.

Post must be more than 350 words. (350 to 500 words)

The images used must be the author's own or free of copyright. (Don't forget to include the source.)

Participants should not use any bot voting services, do not engage in vote buying.

The participation schedule is between Monday, September 9 , 2024 at 00:00 UTC to Sunday, - September 15, 2024 at 23:59 UTC.

Community moderators would leave quality ratings of your articles and likely upvotes.

The publication can be in any language.

Plagiarism and use of AI is prohibited.

Participants must appropriately follow #club5050 or #club75 or #club100.

Use the tags #dynamicdevs-s20w1 , #country (example- #tunisia, #Nigeria) #steemexclusive.

Use the #burnsteem25 tag only if you have set the 25% payee to @null.

Post the link to your entry in the comments section of this contest post. (very important).

Invite at least 3 friends to participate in this contest.

Strive to leave valuable feedback on other people's entries.

Share your post on Twitter and drop the link as a comment on your post.

Your article must get at least 10 upvotes and 5 valid comments to count as valid in the contest, so be sure to interact with other users' entries

Rewards

SC01/SC02 would be checking on the entire 16 participating Teaching Teams and Challengers and upvoting outstanding content. Upvote is not guaranteed for all articles. Kindly take note.

At the end of the week, we would nominate the top 5 users who had performed well in the contest and would be eligible for votes from SC01/SC02.

Important Notice: The selection of the five should be based solely on the quality of their post. Number of comments is no longer a factor to be considered.

---

Best Regards,
Dynamic Devs Team