DataBase Management
The theory
Learn the basics of administering a MySQL DataBase using the SQL language.
Sec 1: Introduction to DBMSs and characteristics
The term Database is used to identify all archives of data organized in a structured manner managed by the mass storage of a computer.
The data are not processed directly as if they were a text document, they are managed by software called DBMS (DataBase Management System)…
Lz 2: Database Management Using DBMS.
Let’s consider an e-commerce of clothing, you will have to keep track of each item of clothing, the users who place an order of the content of the latter.
All the information we want to save will be stored in tables, each relevant to the desired type of data…
Lez 3: DDL, Scheme Management
In Italian, the “data definition language,” is the set of SQL commands that allow you to define the structures in which data, tables, and relationships will be stored.
DDL is used to set domain constraints, on columns, of associations between tables and integrity constraints as well as security-related controls…
Lez 4: DDL, management of TABLES.
Tables are the basic data storage structure; they consist of columns governed by both internal and external constraints.
An “internal constraint” is defined as a type of constraint that corresponds to the data contained in the structure; an external constraint, on the other hand, deals with handling the rules of interaction with other tables, thus with data that are outside the selected table…
Lez 5: DML, the insertion of a record
DML (Data Manipulation Language) is the equivalent of the Italian acronym “data manipulation language.”
This is the set of SQL commands that allow records to be manipulated within the structures defined by the Data Definition Language (DDL)…
Lez 6: DML, the AUTO_INCREMENT fields.
As mentioned earlier the AUTO_INCREMENT field automatically handles the PRIMARY KEY of type INTEGER in such a way that it has a unique value that is always greater than the one previously entered, here is what happens if we try to enter it manually…
Lez 7: DML, editing and deleting a record
Editing a record has as a direct requirement the selection of one and one or more rows within the selected table.
The command to use to make the change is UPDATE SET WHERE ; the list of fields to be changed may be partial since you may decide to leave some data intact and change others…
Lez 8: QL, selection and projection
The acronym QL (Query Language) identifies the subset of the SQL language dedicated to extracting new information from data already possessed.
Since the information we intend to extract is in tabular form, this can be filtered both in content (by row corresponding to the search filters) and by relevant data (the columns that make it up)…
Sec 9: The problem of data normalization.
In a relational Database, multiple data entries are not allowed within the cell (column value relative to a row)…
Sec 10: ER design, how to extract entities.
Each entity corresponds to a table that formally describes a class of objects characterized by of the same attributes. A practical example might be the entity “person” characterized by the attributes first name, last name and social security number…
The practice
Take a cue from my students’ tutorials and test yourself with the suggested tracks.
TASK - ER Modeling and MySQL 08/07/2022
The following task was assigned to the June-August 2022 Data Engineer course and has as its main topics the construction of an ER model later translated into SQL with MySQL dialect.
There is much attention to the distinction of Data Definition Language (DDL), Data Manipulation Language (DML) and lastly Query Language (QL). Below you can find the track:
SOLUTION - Administration of buildings
Benvenuto Building Administration operates in the city of Trieste, where it administers apartment buildings. The company consists of five people (all women, a boost to female unemployment!) and plans to expand. Each person in the company has a badge, the date of issue of which is known and the areas to which it gives access…
SOLUTION - SoundCloudSan Sharing Music Platform
WARNING: Track and solution in English:
The Chinese music company 骗局 wants to produce a music sharing platforms to supportand promote the music producers called SuondCluodSan(SCS).
Every platform user, in order to register is asked to provide name, surname, email and a username…
SOLUTION - Administration of a cafeteria
We need to design the database for a company that runs a school cafeteria. Every day the company prepares lunch with courses divided into first course, second course, fruit, and drinks. Each dish has a name and a price…
SOLUTION - Formula One Racing Team
You want to implement management software for a Formula One racing stable. Each year the team presents the car that will participate in the world championship.
Only one world championship is held each year, and it consists of several tracks whose names, country of origin, length, number of laps scheduled, and the date on which the race will be held…
SOLUTION - Management of multi-screen cinemas
A database containing information related to the management of a multi-screen cinema company that wants to manage reservations through the Internet is represented. Multiplexes have a name, an address, a city a telephone.
Multiple theaters are available at each multiplex, each theater has a certain number of seats (marked by the row and number) and a Film being shown. Registered users once they choose the movie can purchase one or more tickets…
