Databases

From Sinfronteras
Jump to: navigation, search

Contents

Repasar y preguntas

  • For each author_id: The number of books they have wrote:
  • For each author_id, (who has wrote more than 2 books) the number of books they have wrote:
  • For each author_id and author firstname (who has wrote more than 2 books) the number of books they have wrote (este ejemplo es extremandamente importante para entender el funcionamiento de INNER JOIN, GROUP BY, HAVING):

Terminology of relational model

A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views, and other objects. https://en.wikipedia.org/wiki/Database

LET’s look at the titles table in Figure Figure 1.

  • What is the primary key: title_id
  • What is the foreign key: Pub_id
  • What is the cardinality: 13
select count(title_id) as 'Cardinality of the «titles» table' from titles;
  • What is its degree: 9
  • What is the domain of contract: Smallint(6) NOT NULL
  • What is the domain of country in the table Publishers: varchar(15) NOT NULL

Database design

Los siguietes archivos corresponden a las clase del Prof. Greg South at CCT. De estas clases se deriva gran parte del contenido de esta sección. Especialmente el contenido relacionado con la creación de Entity relationship diagrams:

Stages of the database system development lifecycle.

Methodology

  • Gather Requirements
  • Conceptual design: Build a model independent of the choice of DBMS
  • Logical design: Create the Relational Model
  • Physical design: (How the database is stored in hardware) Produce MySQL create statements for each relation

Conceptual design

Entity relationship modelling

  • Entity relationship (ER) model is a popular high-level conceptual data model.
  • The main component of the model is the Entity relationship diagram.
Entity relationship diagram - ERD

La siguiente página proporciona una aplicación que permite crear ERD: https://www.draw.io/

The E-R diagram is a simple way of representing the data entities being modelled and the relationships between these data entities. It's Composed of entities, attributes and relationships.

Numerous ERD styles exist. The differences between the styles are only on how the entities and their relationships are illustrated. This module focuses on Chen's ERD style.

  • Entity: an entity is any object in the system that we want to model and store information about. They are objects or items of interest
    • In a University Database we might have: Entity for «Students», «Modules» and «Lecturers».
  • Attributes: an attribute is a property of an entity.
    • Students attributes such as: «StudentID», «Name» and «Course»
  • Relationship: links between entities.
Example 1

Design an ER diagram for such a bookstore:

  • The database must store book, author, publisher and warehouse information.
  • For every book you must capture the title, isbn, year and price information. The isbn value is unique for a book.
  • For every author you must store an id, name, address and the URL of their homepage. Each author can write many books, and each book can have many authors, for example.
  • For every publisher you must store an id, name, address, phone number and an URL of their website.
  • Books are stored at several warehouses, each of which has a code, address and phone number.
  • A book has only one publisher.
  • The warehouse stocks many different books. A book may be stocked at multiple warehouses.
  • The database records the number of copies of a book stocked at various warehouses.

Con la información proporcionada, debemos identificar the entities, attributes and the relationships between entities.

Entities and attributes:

  • book: b_isbn , b_title, b_year, b_price
  • author: au_id, au_name, au_address, au_url
  • publisher: pu_id, pu_name, pu_address, pu_phone, pu_url
  • warehouse: w_code, w_address, w_phone
  • num_copies (The database records the number of copies...) (este atributo no ha sido especificado como parte de un entidad en particular)

Relationships:

  • Each author can «write» many books, and each book can have many authors: El verbo que en este caso describe la relación entre author y book es «write»
  • A book has only one publisher: «publish»
  • Books are «stored» at several warehouses: «stores/stocke»
  • The warehouse «stocks» many different books. A book may be stocked at multiple warehouses: «stores/stocke»
  • The database records the number of copies of a book «stocked» at various warehouses. «stores/stocke»
Entity relationship diagram of the bookstore. File:Er_diagram-bookstore.xml es el correspondiente archivo .xml creado con la aplicación https://www.draw.io/ Se debe notar que las figuras seleccionadas (rectángulo, óvalo, diamante) son estándares usados en este tipo de diagramas y deben, por lo tanto, respetarse. También cabe destacar que las primary key (en este ejemplo b_isbn, au_id, pu_id, w_code) deben subrayarse también en el diagrama. Debido a un error en la exportación del archivo .xml, estos atributos no se encuentran subrayados en la imagen.
Example 2: the Company database example
  • The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.
  • A department controls a number of projects, each of which has a unique name, a unique number, and a single location.
  • The database will store each employee’s name, Social Security number, address, salary, sex (gender), and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. It is required to keep track of the cur- rent number of hours per week that an employee works on each project, as well as the direct supervisor of each employee (who is another employee).
  • The database will keep track of the dependents of each employee for insurance purposes, including each dependent’s first name, sex, birth date, and relationship to the employee.
The ER conceptual schema diagram for the company database

Logical design

Relational Model

Transform the ER Model to Relational Model (logical data model).

Para explicar la tranformación del ER Model en el Relational Model nos vamos a basar en el Example 2: the Company database example

Seven step algorithm to convert the basic ER model constructs into relations:

Step 1: Mapping of regular entity types
  • For each regular (strong) entity type E in the ER schema, create a relation R that includes all the simple attributes of E.
  • Include only the simple component attributes of a composite attribute. E.g. see name attribute in Employee
  • Choose one of the key attributes of E as the primary key for R.

Remember:

  • Strong Entity Type - Entity type that is not dependent on some other entity type.
  • Weak Entity Type - Entity type that is dependent on some other entity type.
Employee
Fname Minit Lname Ssn Bdate Address Sex Salary
Department
Dname Dnumber
Project
Pname Pnumber Plocation

Note: The foreign key, relationship attributes and multivalued attributes, if any, are not included yet; they will be added during subsequent steps.

Step 2: Mapping of weak entity type

For each weak entity type:

  • Create a relation that includes all simple attributes.
  • Include the simple component attributes of any composite attributes.
  • Include as a foreign key, the primary key of the owner entity.
  • The primary key of the new relation will be the combination of this foreign key and the partial key of the weak entity.

Side note on simple/composite attributes (back on er design):

  • It is important to note whether an attribute is simple or composite
  • Composite attributes are made up of simple attributes
  • E.g, the address attribute can be simple and hold all the details of an address as a single value, such as, ’30 Westland St, Glasgow, G11 93Y’
  • However, the address attribute may also represent a composite attribute made up of simple attributes that hold the address details as separate values in the street (30 Westland St’), city (‘Glasgow’), and postcode (‘G11 93Y’)
  • The option to represent address details as a simple or composite attribute is determined by the users’ requirements:
    • If the user doesn’t need to access the separate components of an address, we represent the address attribute as a simple attribute
    • On the other hand if the user does need to access the individual components of an address, we represent the address attribute as being composite, made up of the required simple attributes


In our example, we create the weak entity type DEPENDENT:

Dependent
Essn Dependent_name Sex Bdate Relationship
  • We include the primary key Ssn of the EMPLOYEE relation -which corresponds to the owner entity type- as a foreign key attribute of DEPENDENT; we rename it Essn, although this is not necessary.
  • The primary key of the DEPENDENT relation is the combination {Essn, Dependent_name}, because Dependent_name (also renamed from Name in ER model) is the partial key of DEPENDENT.
Step 3: Mapping of binary 1:1 relationship types

For each binary 1:1 relationship type:

  • Choose one of the relations and include the other’s PK in it as a FK.
  • Include any simple attributes of the relationship (relationship attributes)
  • In our example, we map the 1:1 relationship type MANAGES from our ER diagram by choosing the participating entity type DEPARTMENT to serve in the role of foreign key because its participation in the MANAGES relationship type is total (every department has a manager).
    • The participation in the relationship is full on the Department side and partial on the Employee side. For this reason, IT IS BETTER to insert the PK of Employee as a FK in the Department Relation.
      • Note: EMPLOYEE participation is partial. DEPARTMENT participation is not clear from the requirements. We question the users, who say that a department must have a manager at all times, which implies total participation. So, every department has a manager
  • We include the primary key of the EMPLOYEE relation as foreign key in the DEPARTMENT relation and rename it to Mgr_ssn.
  • We also include the simple attribute Start_date of the MANAGES relationship type in the DEPARTMENT relation and rename it Mgr_start_date
Department
Dname Dnumber Mgr_ssn Mgr_start_date

FURTHER NOTE:

  • Note that it is possible to include the primary key of department as a foreign key in Employee table instead.
  • In our example, this amounts to having a foreign key attribute, say Department_managed in the EMPLOYEE relation, but it will have a NULL value for employee tuples who do not manage a department.
  • This would be a bad choice, because if only 2% of employees manage a department, then 98% of the foreign keys would be NULL in this case.
Step 4: Mapping of binary 1 to N relationship types
  • For each binary 1 to N relationship in the ER diagram, include, as a foreign key, in the entity on the N side the primary key of the 1 side.
  • Include simple attributes of 1:N relationship type as attributes of the relation on the N side.

In our example:

  • For WORKS_FOR we include the primary key Dnumber of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and call it Dno
  • For SUPERVISION we include the primary key of the EMPLOYEE relation as foreign key in the EMPLOYEE relation itself -because the relationship is recursive- and call it Super_ssn.
Employee
Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno


  • The CONTROLS relationship is mapped to the foreign key attribute Dnum of PROJECT, which references the primary key Dnumber of the DEPARTMENT relation.
Project
Pname Pnumber Plocation Dnum
Step 5: Mapping of binary m:n relationship types
  • For each binary M:N relationship type, create a new relation:
    • Include in the new relation as foreign keys, the primary keys of the two participating entities.
    • The primary key of the new relation will be the combination of these two foreign keys.
    • Include any simple attributes of M:N relationship type.
  • In our example, we map the M:N relationship type WORKS_ON from our ERD, by creating the relation WORKS_ON:
    • We include the primary keys of the PROJECT and EMPLOYEE relations as foreign keys in WORKS_ON and rename them Pno and Essn, respectively (renaming is not required; it is a design choice).
    • We also include an attribute Hours in WORKS_ON to represent the Hours attribute of the relationship type.
    • The primary key of the WORKS_ON relation is the combination of the foreign key attributes {Essn, Pno}.
Works_on
Essn Pno Hours
Step 6: Mapping of multivalued attributes
  • For each multivalued attribute A, create a new relation R:
    • This relation R will include an attribute corresponding to A, plus the primary key attribute K -as a foreign key in R- of the relation that represents the entity type OR relationship type that has A as a multivalued attribute.
    • The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components.
  • In our example, we create a relation DEPT_LOCATIONS:
    • The attribute Dlocation represents the multivalued attribute LOCATIONS of DEPARTMENT, whereas Dnumber -as foreign key- represents the primary key of the DEPARTMENT relation.
    • The primary key of DEPT_LOCATIONS is the combination of {Dnumber, Dlocation}.
Dept_locations
Dnumber Dlocation
Step 7: Mapping of n-ary relationship types
  • For each n-ary relationship type R, where n > 2 (relationship que envuelven más de dos entities) create a new relationship relation S to represent R:
    • Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types.
    • Also include any simple attributes of the n-ary relationship type
  • Este caso no se encuentra en nuestro ejemplo (Company ER). Considere la siguiente relación:
N-ary relationship type (n>2)
Offers
course_no instr_no semestre_no room_no
Resultado
Relationship mapping for the company example
Relational database schema for the company example
Discussion and summary
ER Model Relational model
Entity type Entity relation
1:1 or 1:N relationship type Foreign key (or relationship relation)
M:N relationship type Relationship relation and tow foreign keys
n-ary relationship type Relationship relation and n foreign keys
Simple attribute Attribute
Composite attribute Set of simple component attributes
Multivalued attribute Relation and foreign key
Value set Domain
Key attribute Primary (or secondary) key
  • One of the main points to note in a relational schema, in contrast to an ER schema, is that relationship types are not represented explicitly; instead, they are represented by having two attributes A and B, one a primary key and the other a foreign key (over the same domain) included in two relations S and T.
  • Two tuples in S and T are related when they have the same value for A and B.

Normalisation

Validate the Relational Model using Normalisation.

Normalization is a technique for producing a suitable set of relations that support the data requirements of an nterprise.

A suitable set of relations include
  • The minimal number of attributes necessary to support the data requirements of the enterprise;
  • Attributes with a close logical relationship are found in the same relation;
  • Minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys. (these are essential for the joining of related relations)

Potential benefits of using a database that has a suitable set of relations is that the database will be:

  • Easier for the user to access and maintain the data:
    • If we minimize data redundancy:
      • Updates to the data stored in the database are achieved with a minimal number of operations thus reducing the opportunities for data inconsistencies.
      • Take up minimal storage space on the computer - Reduction in the file storage space required by the relations thus minimizing costs.


We highlight two main approaches for using normalization Approach
  • Bottom-up approach: Bottom-up standalone database design technique Approach
  • Top-down approach: Validation technique which may have been created using a top-down approach such as ER modeling

No matter which approach is used, the goal is the same – creating a set of well-designed relations that meet the data requirements of the enterprise

Bottom-up vs Top down:

  • Bottom-up design methodology (also called design by synthesis) considers the basic relationships among individual attributes as the starting point and uses those to construct relation schemas. This approach is not very popular in practice because it suffers from the problem of having to collect a large number of binary relationships among attributes as the starting point. For practical situations, it is next to impossible to capture binary relationships among all such pairs of attributes.
  • In contrast, a top-down design methodology (also called design by analysis) starts with a number of groupings of attributes into relations that exist together naturally, for example, on an invoice, a form, or a report. The relations are then analyzed individually and collectively, leading to further decomposition until all desirable properties are met.
Informal design guidelines for relation schemas

Before discussing the formal theory of relational database design, we discuss informal guidelines that may be used as measures to determine the quality of relation schema design:

  • Making sure that the semantics of the attributes is clear in the schema
  • Reducing the redundant information in tuples
Imparting clear semantics to attributes in relations
  • Guideline 1: Design a relation schema so that it is easy to explain its meaning.
    • Do not combine attributes from multiple entity types and relationship types into a single relation.


  • Whenever we group attributes to form a relation schema, we assume that attributes belonging to one relation have certain real-world meaning and a proper interpretation associated with them.
  • In general, the easier it is to explain the semantics of the relation -or in other words, what a relation exactly means and stands for- the better the relation schema design will be.
  • To illustrate this, consider Figure 1, a simplified version of the COMPANY relational database schema and Figure 2, which presents an example of populated relation states of this schema.
A simplified relational database schema for the company example
  • The meaning of the EMPLOYEE relation schema is simple: Each tuple represents an employee, with values for the employee’s name (Ename), Social Security number (Ssn),... and the number of the department that the employee works for (Dnumber).
  • The Dnumber attribute is a foreign key that represents an implicit relationship between EMPLOYEE and DEPARTMENT.

Examples of violating guideline 1

  • The tuple in the EMP_DEPT relation schema represents a single employee but includes, along with the Dnumber (the identifier for the department he/she works for), additional information-namely, the name (Dname) of the department for which the employee works and the Social Security number (Dmgr_ssn) of the department manager.
    • Esta tuple contiene información de dos entidades distintas (Employee and Department). It violates

Guideline 1 by mixing attributes from distinct real-world entities. Esto no es apropiado...

EMP_DEPT
Ename Ssn Bdate Address Dnumber Dname Dmgr_ssn
Data redundancy
  • Major aim of relational database design is to group attributes into relations to minimize data redundancy.

Grouping attributes into relation schemas has a significant effect on storage space.

For example, compare the space used by the two relations EMPLOYEE and DEPARTMENT with that for an EMP_DEPT base relation (which is the result of applying the NATURAL JOIN operation to EMPLOYEE and DEPARTMENT).

  • In EMP_DEPT, the attribute values pertaining to a particular department (Dnumber, Dname, Dmgr_ssn) are repeated for every employee who works for that department. In contrast, each department’s information appears only once in the DEPARTMENT relation in Figure 2. Only the department number (Dnumber) is repeated in the EMPLOYEE relation for each employee who works in that department as a foreign key.
Employee and department relations of the Company example
Relation schemas suffering from redundant anomalies

An anomaly is anything we try to do with a database that leads to unexpected and/or unpredictable results.

These anomalies can be classified into:

  • Insertion anomalies,
  • Deletion anomalies,
  • and Modification (Update) anomalies.

Insertion anomalies

  • To insert a new employee tuple into EMP_DEPT, we must include either the attribute values for the department that the employee works for, or NULLs (if the employee does not work for a department as yet).
  • For example, to insert a new tuple for an employee who works in department number 5, we must enter all the attribute values of department 5 correctly so that they are consistent with the corresponding values for department 5 in other tuples in EMP_DEPT.
  • In the EMPLOYEE and DEPARTMENT design, we do not have to worry about this consistency problem because we enter only the department number in the employee tuple; all other attribute values of department 5 are recorded only once in the database, as a single tuple in the DEPARTMENT relation


  • It is difficult to insert a new department that has no employees as yet in the EMP_DEPT relation. The only way to do this is to place NULL values in the attributes for employee. This violates the entity integrity for EMP_DEPT because its primary key Ssn cannot be null.
  • Moreover, when the first employee is assigned to that department, we do not need this tuple with NULL values anymore. This problem does not occur in the EMPLOYEE and DEPARTMENT design because a department is entered in the DEPARTMENT relation whether or not any employees work for it, and whenever an employee is assigned to that department, a corresponding tuple is inserted in EMPLOYEE.

Deletion anomalies

  • If we delete from EMP_DEPT an employee tuple that happens to represent the last employee working for a particular department, the information concerning that department is lost inadvertently from the database.
  • This problem does not occur in the EMPLOYEE and DEPARTMENT design because DEPARTMENT tuples are stored separately.

Modification (Update) anomalies

  • In EMP_DEPT, if we change the value of one of the attributes of a particular department -say, the manager of department 5- we must update the tuples of all employees who work in that department; otherwise, the database will become inconsistent.
  • If we fail to update some tuples, the same department will be shown to have two different values for manager in different employee tuples, which would be wrong


Storing natural joins of relations leads to an additional problem referred to as Update anomalies.

Normalisation

Ver esta página: https://www.1keydata.com/database-normalization/first-normal-form-1nf.php

pag. 8, 14

FUNCTIONAL DEPENDENCIES Previously, we have dealt with the informal measures of database design. We now introduce a formal tool for analysis of relational schemas that enables us to detect and describe some of the above-mentioned problems in precise terms

The single most important concept in relational schema design theory is that of a functional dependency. Functional dependency describes the relationship between attributes.

For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A --> B), if each value of A in R is associated with exactly one value of B in R.

An alternative way to describe the relationship between attributes A and B is to say that "A functionally determines B"

When we say: A --> B:

  • A and B may each consist of one or more attributes.
  • The determinant of a functional dependency refers to the attribute or group of attributes on the left-hand side of the arrow. (For example, A is the determinant of B)

Example functional dependency that holds: Consider the values shown in staffNo and sName attributes of the Staff relation. Based on sample data, the following functional dependencies appear to hold:

  • staffNo --> sName
  • sName --> staffNo

However, if the values shown in the Staff relation simply represent a set of values at a given moment in time, then we are not so interested in such relationships between attributes. The reason is that we want to identify functional dependencies that hold for all possible values for attributes of a relation as these represent the types of integrity constraints that we need to identify.

If we know the staff number(staffNo) of a member of staff, we can determine the name of the member of staff (sName). However, it is possible for the sName attribute to hold duplicate values for members of staff with the same name, then we would not be able to determine the staff number of some members of staff.

The only functional dependency that remains true for all possible values for the staffNo and sName attributes of the Staff relation is:

  • staffNo --> sName
  • staffNo functionally determines sName

Characteristics of functional dependencies:

  • Determinants should have the minimal number of attributes necessary to maintain the functional dependency with the attribute(s) on the right hand-side.
  • This requirement is called full functional dependency.
  • So that if A and B are attributes of a relation, B is functionally dependent on A if B is functionally dependent on A, but not on any proper subset of A. [[[WHATTT!!!!]]]

Example partial and full functional dependency:

  • Consider the following functional dependency that exists in the Staff relation:
    • staffNo, sName --> branchNo

It is correct to say that each value of (staffNo, sName) is associated with a single value of branchNo. However, it is NOT a full functional dependency, because branchNo is also functionally dependent on as subset of (staffNo, sName), namely staffNo.

In other words the functional dependency shown is an example of partial dependency.

Type of functional dependency that we are interested in identifying is a full functional dependency:

  • staffNo→ branchNo


Transitive dependency: So far we have discussed functional dependencies that we are interested in for the purposes of normalization.

However, there is an additional type of functional dependency called a transitive dependency that we need to recognize because its existence in a relation can potentially cause update anomalies.

Transitive dependency describes a condition where A, B, and C are attributes of a relation such that if A → B and B → C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C).

Example of transitive functional dependency:

  • Consider functional dependencies in the StaffBranch relation
    • staffNo --> sName, position, salary, branchNo, bAddress
    • branchNo --> bAddress
    • Transitive dependency, branchNo --> bAddress exists on staffNo via branchNo
      • In other words, the StaffNo attribute functionally determines the bAddress via the branchNo attribute and neither branchNo or bAddress functionally determines staffNo

Remember: In StaffBranch relation there is redundant data, the details of a branch are repeated for every member of staff located at a branch.

Normalization of relations:

  • The normalization process, as first proposed by Codd, takes a relation schema through a series of tests to certify whether it satisfies a certain normal form.
  • The process, which proceeds in a top-down fashion by evaluating each relation against the criteria for normal forms and decomposing relations as necessary, can thus be considered as relational design by analysis.
  • Initially, Codd proposed three normal forms, which he called first (1NF), second (2NF), and third normal form (3NF).

Why normalize: Normalization of data can be considered a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the desirable properties of:

  • Minimizing redundancy and
  • minimizing the insertion, deletion, and update anomalies discussed

Normalization is often executed as a series of steps. Each step corresponds to a specific normal form.

Normalization approach: 1st Normal Form:

  • Each table contains all atomic data items(no repeating values), no repeating groups, and a designated primary key (no duplicated rows)

2nd Normal Form (composite keys):

  • In 1NF
  • Includes no partial dependencies
    • No attribute dependent on a portion of primary key

3rd Normal Form:

  • In 2NF
  • Contains no transitive dependencies
    • Attributes are functionally dependent on a non-key attributes (no non-key values based on other non-key values)

For a relational data model, it is important to recognize that it is only First Normal Form (1NF) that is critical in creating relations; all subsequent normal forms are optional. However, to avoid the update anomalies discussed previously, it is generally recommended that we proceed to at least Third Normal Form (3NF)


1NF - FIRST NORMAL FORM: A database is in first normal form if it satisfies the following conditions:

  • Contains only atomic values:
    • An atomic value is a value that cannot be divided. For example, in the table shown below, the values in the [Color] column in the first row can be divided into "red" and "green", hence [TABLE_PRODUCT] is not in 1NF.
  • There are no repeating groups:
    • A repeating group means that a table contains two or more columns that are closely related. For example, a table that records data on a book and its author(s) with the following columns: [Book ID], [Author 1], [Author 2], [Author 3] is not in 1NF because [Author 1], [Author 2], and [Author 3] are all repeating the same attribute.

2NF – SECOND NORMAL FORM:

SQL

SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). https://en.wikipedia.org/wiki/SQL

Database management system

A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs include MySQL, PostgreSQL, MongoDB, Microsoft SQL Server, Oracle, Sybase, SAP HANA, and IBM DB2. A database is not generally portable across different DBMSs, but different DBMS can interoperate by using standards such as SQL and ODBC or JDBC to allow a single application to work with more than one DBMS. Database management systems are often classified according to the database model that they support; the most popular database systems since the 1980s have all supported the relational model as represented by the SQL language. https://en.wikipedia.org/wiki/Database

MySQL

MySQL est un système de gestion de bases de données relationnelles (SGBDR). Il fait partie des logiciels de gestion de base de données les plus utilisés au monde. En informatique, un système de gestion de base de données (abr. SGBD) est un logiciel système destiné à stocker et à partager des informations dans une base de données. SQL fait référence au Structured Query Language, le langage de requête utilisé. SQL (sigle de Structured Query Language, en français langage de requête structurée) est un langage informatique normalisé servant à exploiter des bases de données relationnelles.



Installing MySQL

https://www.mysql.com/

https://en.wikipedia.org/wiki/MySQL

Para instalar la última versión disponible en los repositorios de Ubuntu:

sudo apt update
sudo apt-get install mysql-server



Para instalar la última versión (8.0.12): https://www.tecmint.com/install-mysql-8-in-ubuntu/ La instalación la última versión (si ésta es muy reciente) puede generar errores que no han sido aún corregidos y documentados por la comunidad. La última vez, al instalar la versión 8.0.12, no pude ingresar a mysql a través de «mycli» y la instalación de phpMyAdmin generó errores; ninguno de los dos problemas pudieron ser solutionados debido a que no se encontró suficiente documentación en la web. Recomiendo instalar la última versión incluida por defecto en los repositorios de Ubuntu.

Add MySQL Apt Repository:

wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb 

Then install the MySQL repository package using the following dpkg command:

sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb 

Note that in the package installation process, you will be prompted to choose MySQL server version and other components such as cluster, shared client libraries, or the MySQL workbench that you want to configure for installation. MySQL server version mysql-8.0 will be auto-selected, then scroll down to the last option Ok and click [Enter]

sudo apt update
sudo apt-get install mysql-server



Si la versión instalada de la forma anterior no es la deseada, se puede intentar lo siguiente:

sudo apt install mysql-server-5.6
sudo apt install mysql-client-5.6 (He generalmente usado el server. El client realmente no sé para que se usa)



La última vez, luego de instalar MySQL, no podía ingresar a través de «mysql -u root -p». Se generaba el error ERROR 1698 (28000): Access denied for user 'root'@'localhost'». La solución se encuentra en https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

Al realizar los pasos sugeridos por el método 1 de la solución indicada en el link de arriba:

  • «mysql -u root -p» no funcionó la primera vez.
  • Luego intente ajustar de nuevo el password ejecutando «sudo mysql_secure_installation» nuevamente.
  • Luego no funcionaba ni siquiera de esta forma: «sudo mysql -u root», lo que estaba funcionando antes.
  • Luego intenté con «sudo mysql -u root -p» y funcionó.
  • Luego de eso, también funcionó con «mysql -u root -p»



Secure MySQL Server Installation

Run the security script which comes with the binary package by issuing the following command:

sudo mysql_secure_installation

By default, the MySQL installation is unsecure. To secure it, run the security script which comes with the binary package. You will be asked to enter the root password you set during the installation process. Then also choose whether to use the VALIDATE PASSWORD plugin or not. En esta primera pregunta elegí «N» porque creo que este plugin genera problemas al tratar de ingresar a «mysql» con «mycli».

You can also change the root password you set before (as we have done in this example). Then enter yes/y to the following security questions:

  • Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
  • Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
  • Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
  • Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y



Testing MySQL

Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status:

systemctl status mysql.service

If MySQL isn't running, you can start it with:

sudo systemctl start mysql.

For an additional check, you can try connecting to the database using the mysqladmin tool:

mysqladmin -p -u root version



mycli

https://www.mycli.net/

MyCLI is a command line interface for MySQL, MariaDB, and Percona with auto-completion and syntax highlighting.

sudo apt-get install mycli

Para iniciar mycli:

mycli -u root



Instalación de PhpMyAdmin

https://www.phpmyadmin.net/

https://en.wikipedia.org/wiki/PhpMyAdmin



From Ubuntu repositories

Como siempre, los prepositorios pueden no estar actualizados con la última versión

sudo apt-get install phpmyadmin



Config

Lors de l’installation, quelques questions sont posées afin de configurer le système correctement:

  • Pour la sélection du serveur nous avons choisi Apache 2.
  • Nous avons sélectionné “oui” lorsqu’il nous a demandé si nous souhaitons utiliser dbconfig-commun pour mettre en place la base de données.
  • Nous avons ensuite entré les mots de passe de notre administrateur de base de données et pour phpMyadmin.


Le fichier de configuration de phpMyadmin sera ajouté dans le répertoire

/etc/apache2/conf-enabled/

Nous avons ensuite activé l’extension php5-mcrypt :

sudo php5enmod mcrypt (Esta parte no la puede llevar a cabo la última vez que lo instalé. Sin embargo parece funcionar bien)

A la fin il est nécessaire de redémarrer Apache pour que les modifications soient reconnues:

sudo service apache2 restart

L’accès au logiciel phpMyAdmin se fait en visitant notre nom de domaine ou adresse IP publique du serveur :

http://domain_name_or_IP/phpmyadmin
http://localhost/phpmyadmin



Uninstall MySQL

https://askubuntu.com/questions/172514/how-do-i-uninstall-mysql

To remove mysql completely from your system Just type in terminal:

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean


En el siguiente link se incluyen algunos otros comandos to remove mysql completely from your system: https://linuxscriptshub.com/uninstall-completely-remove-mysql-ubuntu-16-04/



Customizing MySQL prompt

Personalizar (customize) MySQL prompt: http://www.thegeekstuff.com/2010/02/mysql_ps1-6-examples-to-make-your-mysql-prompt-like-angelina-jolie/

Display username, hostname and current database name in the mysql prompt:

export MYSQL_PS1="\u@\h[\d]> "
export MYSQL_PS1="Adelo_Vieira(2017279):\u@\h[\d]> "

O desde el prompt de MySQL:

prompt Adelo_Vieira(2017279):\u@\h[\d]> 
prompt   // Returning to default PROMPT of mysql

Ingresar al prompt MySQL desde la línea de comandos:

mysql -u root -p
O a través de «mycli»:  mycli -u root



Some fundamental commands

Listar las bases de datos:

show databases;

Ingresar o cambiar de base de datos:

use nombre_database;

Mostrar la base de datos que se está usando:

select database();

Crear una base de datos:

create database nombre_database;

Borrar una base de datos:

drop database nombre_de_la_base

Listar las tablas en la base de datos:

show tables;

Borrar tablas por separado:

drop table nombre_tabla;
DROP TABLE IF EXISTS authors;
  • Use IF EXISTS to prevent an error from occurring for tables that do not exist. Instead of an error, a NOTE is generated for each nonexistent table; these notes can be displayed with SHOW WARNINGS.

Para desplegar los campos de una tabla. Esta orden no muestra los valores de cada campo; sólo los campos y especifica el Tipo, Key y otras descripciones:

describe nombre_tabla; 

Para desplegar todos los campos de una tabla con sus respectivos valores:

select * from nombre_tabla;

Para desplegar algunos campos de una tabla:

select au_fname, au_lname, phone from authors;



Mycli - Command Line Autocomplete Tool for MySQL with Syntax Highlighting

Para ingresar a la línea de comandos de «mycli»:

$ mycli -u user
$ mycli -u user -p db_name



Backup and Restore MySQL/MariaDB Databases


Using Command-Line


Backup

The mysqldump command is used to export the contents of a database to a text file.

Ejecutar la siguiente orden en el command line (bash) prompt (NO en la línea de comandos de MySQL):

mysqldump --user=root --password="" nombre_database > nombre_database.sql

OR

mysqldump -u root -p nombre_database > nombre_database.sql

Al parecer, la opción -A permite realizar un backup de todas las bases de datos:

mysqldump -A -u root -p > backup.sql



Restore/Import

The mysql command-line client can be used to import data from a text file into a MySQL/MariaDB database.

1. create a new, empty database to hold your data:

Este paso dependerá del archivo .sql que se quiera importar. A veces la creación de la DB se encuentra incluida en el archivo .sql por lo que no se necesario crear una base de datos antes de importar el archivo.

Ejecutar la siguiente orden en el command line (bash) prompt (NO en la línea de comandos de MySQL):

mysql --user=root --password="" -e "CREATE DATABASE nombre_database"

// OR

mysql -u root -p -e "CREATE DATABASE nombre_database"

2. Import the contents of the backup file into the new database:

mysql --user=root --password="" --database=nombre_database < nombre_database.sql

// OR

mysql -u root -p nombre_database < nombre_database.sql

// Si el archivo .sql incluye la creación de la DB, no es necesario indicar el nombre de la DB:
mysql -u root -p < nombre_database.sql

You can then show the content of the database de esta forma:

mysql --user=root --password="" --database=nombre_database -e "SHOW TABLES"

OR

mysql -u root -p nombre_database -e "SHOW TABLES"

You should now update your application’s configuration and point it to your newly-created database. For example, with the Bitnami WordPress module, you will need to edit the wp-config.php file in the apps\wordpress\htdocs\ subdirectory of your XAMPP installation directory. Within this file, you will need to update the DB_NAME constant to use the new database name.



Example - Importing the Sakila database

http://downloads.mysql.com/docs/sakila-db.zip

Dos archivos son proporcionados para crear esta DB:

sakila-schema.sql
sakila-data.sql

Debemos primero importar el archivos sakila-schema.sql y luego sakila-data.sql:

mysql -u root -p < sakila-schema.sql
mysql -u root -p < sakila-data.sql

Note que en este caso no es necesario crear la DB antes de importar el archivo .sql ni indicar el nombre de la DB en la syntax.



Using phpMyAdmin


Import a CSV file into a MySQL database

https://medium.com/@AviGoom/how-to-import-a-csv-file-into-a-mysql-database-ef8860878a68

Example using File:SP500Companies.csv

DROP TABLE IF EXISTS SP500Companies;
CREATE TABLE SP500Companies
  (
  id          INT          AUTO_INCREMENT,
  Name        VARCHAR(50)  NOT NULL,
  Weight      float,
  Sector      VARCHAR(50),
  Shares_Held float,
  CONSTRAINT pk_SP500Companies PRIMARY KEY (id)
  );

LOAD DATA LOCAL INFILE '/root/SP500Companies.csv'
INTO TABLE SP500Companies
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
IGNORE 1 ROWS (Name, Weight, Sector, Shares_Held);


Es problable: ERROR 1148: The used command is not allowed with this MySQL version. La solución se encuentra explicada aquí: https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version

You must specify that as an additional option when setting up your client connection:

mysql -u myuser -p --local-infile somedatabase

This is because that feature opens a security hole. So you have to enable it in an explicit manner in case you really want to use it.

Both client and server should enable the local-file option. Otherwise it doesn't work.To enable it for files on the server side server add following to the my.cnf configuration file:

loose-local-infile = 1



Reset the MySQL/MariaDB Password

Si ningún password ha sido configurado:

mysqladmin --user=root password "newpassword"

For example, to change the MySQL/MariaDB root password from its default empty value to the password gue55me, you would execute:

mysqladmin --user=root password "gue55me"

If a password has already been previously set and you’d like to change it to a new one, you can use the following syntax:

mysqladmin --user=root --password=oldpassword password "newpassword"

For example, to change the root password from 12345 to gue55me, you would execute:

mysqladmin --user=root --password=12345 password "gue55me"



Creación de una base de datos de prueba en MySQL

Si queremos crear una base de datos extensa, podemos utilizar la base de datos sakila, la cual se puede descargar de:

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

Para nuestras prácticas, vamos a crear una pequeña base de datos: books.

Esquema de la base de datos books.

Ingresar al prompt MySQL y ejecutar los siguientes comandos:

CREATE DATABASE books;

USE books;

DROP TABLE IF EXISTS authors;
CREATE TABLE authors
  (
  au_id    CHAR(3)     NOT NULL,
  au_fname VARCHAR(15) NOT NULL,
  au_lname VARCHAR(15) NOT NULL,
  phone    VARCHAR(12)         ,
  address  VARCHAR(20)         ,
  city     VARCHAR(15)         ,
  state    CHAR(2)             ,
  zip      CHAR(5)             ,
  CONSTRAINT pk_authors PRIMARY KEY (au_id)
  );
INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223','75 West 205 St','Bronx','NY','10468');
INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020','2922 Baseline Rd','Boulder','CO','80303');
INSERT INTO authors VALUES('A03','Hallie','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A04','Klee','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A05','Christian','Kells','212-771-4680','114 Horatio St','New York','NY','10014');
INSERT INTO authors VALUES('A06','','Kellsey','650-836-7128','390 Serra Mall','Palo Alto','CA','94305');
INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752','1442 Main St','Sarasota','FL','34236');


DROP TABLE IF EXISTS publishers;
CREATE TABLE publishers
  (
  pub_id   CHAR(3)     NOT NULL,
  pub_name VARCHAR(20) NOT NULL,
  city     VARCHAR(15) NOT NULL,
  state    CHAR(2)             ,
  country  VARCHAR(15) NOT NULL,
  CONSTRAINT pk_publishers PRIMARY KEY (pub_id)
  );
INSERT INTO publishers VALUES('P01','Abatis Publishers','New York','NY','USA');
INSERT INTO publishers VALUES('P02','Core Dump Books','San Francisco','CA','USA');
INSERT INTO publishers VALUES('P03','Schadenfreude Press','Hamburg',NULL,'Germany');
INSERT INTO publishers VALUES('P04','Tenterhooks Press','Berkeley','CA','USA');


DROP TABLE IF EXISTS titles;
CREATE TABLE titles
  (
  title_id   CHAR(3)      NOT NULL,
  title_name VARCHAR(40)  NOT NULL,
  type       VARCHAR(10)          ,
  pub_id     CHAR(3)      NOT NULL,
  pages      INTEGER              ,
  price      DECIMAL(5,2)         ,
  sales      INTEGER              ,
  pubdate    DATE                 ,
  contract   SMALLINT     NOT NULL,
  CONSTRAINT pk_titles PRIMARY KEY (title_id)
  );
INSERT INTO titles VALUES('T01','1977!','history','P01',107,21.99,566,'2000-08-01',1);
INSERT INTO titles VALUES('T02','200 Years of German Humor','history','P03',14,19.95,9566,'1998-04-01',1);
INSERT INTO titles VALUES('T03','Ask Your System Administrator','computer','P02',1226,39.95,25667,'2000-09-01',1);
INSERT INTO titles VALUES('T04','But I Did It Unconsciously','psychology','P04',510,12.99,13001,'1999-05-31',1);
INSERT INTO titles VALUES('T05','Exchange of Platitudes','psychology','P04',201,6.95,201440,'2001-01-01',1);
INSERT INTO titles VALUES('T06','How About Never?','biography','P01',473,19.95,11320,'2000-07-31',1);
INSERT INTO titles VALUES('T07','I Blame My Mother','biography','P03',333,23.95,1500200,'1999-10-01',1);
INSERT INTO titles VALUES('T08','Just Wait Until After School','children','P04',86,10.00,4095,'2001-06-01',1);
INSERT INTO titles VALUES('T09','Kiss My Boo-Boo','children','P04',22,13.95,5000,'2002-05-31',1);
INSERT INTO titles VALUES('T10','Not Without My Faberge Egg','biography','P01',NULL,NULL,NULL,NULL,0);
INSERT INTO titles VALUES('T11','Perhaps It''s a Glandular Problem','psychology','P04',826,7.99,94123,'2000-11-30',1);
INSERT INTO titles VALUES('T12','Spontaneous, Not Annoying','biography','P01',507,12.99,100001,'2000-08-31',1);
INSERT INTO titles VALUES('T13','What Are The Civilian Applications?','history','P03',802,29.99,10467,'1999-05-31',1);


DROP TABLE IF EXISTS title_authors;
CREATE TABLE title_authors
  (
  title_id      CHAR(3)      NOT NULL,
  au_id         CHAR(3)      NOT NULL,
  au_order      SMALLINT     NOT NULL,
  royalty_share DECIMAL(5,2) NOT NULL,
  CONSTRAINT pk_title_authors PRIMARY KEY (title_id, au_id)
  );
INSERT INTO title_authors VALUES('T01','A01',1,1.0);
INSERT INTO title_authors VALUES('T02','A01',1,1.0);
INSERT INTO title_authors VALUES('T03','A05',1,1.0);
INSERT INTO title_authors VALUES('T04','A03',1,0.6);
INSERT INTO title_authors VALUES('T04','A04',2,0.4);
INSERT INTO title_authors VALUES('T05','A04',1,1.0);
INSERT INTO title_authors VALUES('T06','A02',1,1.0);
INSERT INTO title_authors VALUES('T07','A02',1,0.5);
INSERT INTO title_authors VALUES('T07','A04',2,0.5);
INSERT INTO title_authors VALUES('T08','A06',1,1.0);
INSERT INTO title_authors VALUES('T09','A06',1,1.0);
INSERT INTO title_authors VALUES('T10','A02',1,1.0);
INSERT INTO title_authors VALUES('T11','A03',2,0.3);
INSERT INTO title_authors VALUES('T11','A04',3,0.3);
INSERT INTO title_authors VALUES('T11','A06',1,0.4);
INSERT INTO title_authors VALUES('T12','A02',1,1.0);
INSERT INTO title_authors VALUES('T13','A01',1,1.0);


DROP TABLE IF EXISTS royalties;
CREATE TABLE royalties
  (
  title_id     CHAR(3)      NOT NULL,
  advance      DECIMAL(9,2)         ,
  royalty_rate DECIMAL(5,2)         ,
  CONSTRAINT pk_royalties PRIMARY KEY (title_id)
  );
INSERT INTO royalties VALUES('T01',10000,0.05);
INSERT INTO royalties VALUES('T02',1000,0.06);
INSERT INTO royalties VALUES('T03',15000,0.07);
INSERT INTO royalties VALUES('T04',20000,0.08);
INSERT INTO royalties VALUES('T05',100000,0.09);
INSERT INTO royalties VALUES('T06',20000,0.08);
INSERT INTO royalties VALUES('T07',1000000,0.11);
INSERT INTO royalties VALUES('T08',0,0.04);
INSERT INTO royalties VALUES('T09',0,0.05);
INSERT INTO royalties VALUES('T10',NULL,NULL);
INSERT INTO royalties VALUES('T11',100000,0.07);
INSERT INTO royalties VALUES('T12',50000,0.09);
INSERT INTO royalties VALUES('T13',20000,0.06);



Practice Lab1

  • WHERE - OR - AND - DISTINCT - IN - LIKE
  • AS: Usado para hacer aliases (renombrar una columna): select f_name as 'First name' from ...

All unique book types in the database:

select distinct type from titles;

List the full name, phone number, zip code of authors who live in New York or San Francisco:

select au_fname, au_lname, phone, zip from authors where city="New York" or city="San Francisco";

All the publisher names and IDs, who are based in the USA and in New York city:

select pub_name as 'Publisher name', pub_id as 'Publisher ID' from publishers where country='USA' and city='New York';

// or

select pub_name, pub_id from publishers where country in ('USA') and city in ('New York');

All the title IDs where the advance was between 50000 and 100000:

select title_id, advance from royalties where advance >= 50000 and advance <= 100000;

All the book title IDs, and book names where the type is either history or children (Using a set Operator):

select title_id, title_name from titles where type="history" or type="children";

// or

select title_id, title_name from titles where type in ("history","children");

List all authors who live at an address that starts with the number 14:

select * from authors where address LIKE "14%";

List all the book titles that contain a word that is 4 letters long, and starts with «w» and ends with «t»:

El profesor colocó que la respuesta es:

select title_name from titles where title_name like '%w__t%';

Yo creo que esa respuesta no es completamente válida pues incluirá los títulos que tengan una palabra que contenga 'w__t' sin que necesariamente sea la primera y última letra de la palabra. Es decir una palabra de más de 4 lettres long que contenga el patrón 'w__t'.

La respuesta que creo debe acercarse más a la adecuada es:

select title_name from titles
where title_name like 'w__t %'
or title_name like '% w__t %'
or title_name like '% w__t'
or title_name like 'w__t';

En estos casos MySQL no hace distinción entre mayúsculas o minúsculas.



Practice Lab2


Fecha y hora

Para crear un campo de fecha y hora que se cree con la fecha y hora actual:

CREATE TABLE fechas(
  id    INT(10)       PRIMARY KEY AUTO_INCREMENT,
  date  TIMESTAMP     NOT NULL   DEFAULT  CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO fechas() VALUES();

Si queremos que el campo date pueda ser nulo debemos usar «DATETIME» en vez de «TIMESTAMP»

NOTE: MySQL retrieves and displays DATE values in 'YYYY-MM-DD'

Lo anterior creará el campo con el siguiente formato:

2017-12-01 00:33:25

Ahora, si queremos seleccionar las fechas con un formato distinto:

select DATE_FORMAT(date,'%d-%m-%y')  AS date from fechas;

select DATE_FORMAT(date,'%d-%m-%Y')  AS date from fechas;

select DATE_FORMAT(date,'%D %M, %Y') AS date from fechas;


The Name of the book published on the 1st August 2000?:

select title_name as 'Titles of books published on the 1st August 2000'
from titles
where pubdate='2000-08-01';



Ordering and pattern Matching

  • ORDER: Ordenar alfabética o numéricamente: ASC (por defecto) , DESC

List the type, pages (i.e. page size) and title name of all book titles, alphabetically ordered by type:

select type as 'Type', pages as 'Page size', title_name as 'Title name' 
from titles 
order by type;

The type, page size, price and title name of all book titles, alphabetically ordered by type and increasing price:

select type as 'Type', pages as 'Page size', price as 'Price', title_name as 'Title name' 
from titles 
order by type, price;



Aggregate Functions

  • COUNT: Contar filas: select count(*) from titles where ...;
  • MAX / MIN / AVG: select avg(pages) from titles;
  • SUM(): Sumar

How many book titles:

select count(title_name) as 'Amount of book titles' from titles;

How many book titles have less than or equal to 200 pages:

select count(*) as 'Books less that or equal to 200 pages' from titles where pages <= 200;

The minimum, maximum, and average page size of a book:

select min(pages) as 'Min pages', max(pages) as 'Max pages', avg(pages) as 'Average pages' from titles;

The total price of all books:

select sum(price) as 'Total price of all books (€)' from titles;



Joins

  • INNER JOIN

List all book titles that were published in New York:

select title_name as 'Title'
from titles, publishers
where titles.pub_id=publishers.pub_id and city="New York";

or,

select title_name as 'Title' 
from titles t, publishers p 
where t.pub_id=p.pub_id and city="New York";

or, in the NEW ANSI way:

select title_name as 'Title'
from titles t inner join publishers p
on t.pub_id=p.pub_id and city="New York";

List all publishers who have published children books:

select distinct pub_name as 'Publisher' 
from  publishers p inner join titles t 
on p.pub_id=t.pub_id and type='children';

List all book titles that were published in New York, ordered alphabetically desc:

select title_name as 'Title'
from titles t inner join publishers p
on t.pub_id=p.pub_id and city='New York'
order by title_name desc;

The name of the publisher who published the book titled 1977!:

select pub_name as 'Publisher who published the book «1977!»'
from titles t inner join publishers p on t.pub_id=p.pub_id
where title_name='1977!';



Having and grouping

  • GROUP BY
  • HAVING

For each author_id: The number of books they have wrote:

select au_id as 'Author ID', count(title_id) as 'Number of books written' 
from title_authors 
group by au_id;

For each author_id (who has wrote more than 2 books) the number of books they have wrote:

select au_id as 'Author ID (authors who wrote more than 2 books)', count(title_id) as 'Number of books they wrote'
from title_authors
group by au_id
having count(title_id) > 2;

For each author_id and author firstname (who has wrote more than 2 books) the number of books they have wrote:

select t.au_id as 'Author ID (authors who wrote more than 2 books)', au_fname as 'Author firstname', count(title_id) as 'Number of books they wrote'
from title_authors t inner join authors a on t.au_id=a.au_id
group by t.au_id
having count(title_id) > 2;



Practice Lab3


Table creation

Create an «instructors» table with 3 columns: ID, name and salary. The ID is the primary key. Choose appropriate data types:

create table instructors(
id        char(5),
name      varchar(50) not null,
salary    numeric(8,2),
primary key(id)
);

Note que «numeric()» también puede ser introducido como «decimal()»


CREATE TABLE Work_on(
    c_id         INT NOT NULL,
    p_id         INT NOT NULL,
    hours_worked INT,
    CONSTRAINT Work_on_PK          PRIMARY KEY (c_id, p_id),
    CONSTRAINT Work_on_Chemist_FK  FOREIGN KEY (c_id) REFERENCES Chemist (c_id),
    CONSTRAINT Work_on_Projects_FK FOREIGN KEY (p_id) REFERENCES Project (p_id)
);


CREATE TABLE fechas(
  id    INT(10)       PRIMARY KEY AUTO_INCREMENT,
  date  TIMESTAMP     NOT NULL   DEFAULT  CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP
);



Auto increment

Si queremos que el «id» sea introducido automáticamente debemos usar «AUTO_INCREMENT». En este caso el id debe ser de tipo «int». De esta forma, si omitimos el id al ingresar un valor en la table, éste será agregado automáticamente con el valor numérico que corresponda (el siguiente número del mayor valor registrado)

create table instructors(
id        int auto_increment,
name      varchar(50) not null,
salary    numeric(8,2),
primary key(id)
);



Insert values

Insert two rows into the instructors table:

insert into instructors values('1','Kike Dominguez',40000.70);

Si queremos dejar algunos campos vacíos (null), hay dos formas:

insert into instructors values('2','Ernesto Vieira',null);

insert into instructors(id,name) values('3','Wilder Raben');



Update/Changes

Update/Changes the value of the name of one of the instructors:

update instructors set name='Enrique Dominguez' where id='1';

Give all instructors a 5% pay increase:

update instructors set salary=salary*1.05;



Delete rows

Delete one of the rows based on the value of the ID:

delete from instructors where id='3';



Add attributes

Add a new attribute/column (address) to the table:

alter table instructors add address varchar(200);



Chage the name of a attribute/column

En MySQL, para cambiar el nombre de una columna debemos utilizar esta sintaxis. Notar que es necesario especificar el tipo de datos aunque no lo estemos cambiando, en este caso (NUMERIC(8,2)). El NOT NULL no es necesario especificarlo a menos que queramos cambiar esta opción:

alter table instructors change column salary salary_a_year numeric(8,2) not null;



PhpMyAdmin

Instalación de PhpMyAdmin

PMA est une application Web de gestion pour les systèmes de gestion de base de données MySQL réalisée en PHP et distribuée sous licence GNU GPL.



Otras aplicaciones de gestión de MySQL

Aparte de PhpMyAdmin, también existen otras aplicaciones de este tipo. Entre las más comunes se encuentran:

  • Para Windows: HeidiSQL - MySQL Database Viewer: https://www.heidisql.com/
  • Para Ubuntu: Mysql workbench
  • Para que estas aplicaciones se conecten a nuestra base de datos MySQL, debemos configurar:
    • Hostanme/IP: 127.0.0.1
    • User: root
    • Port: 3306 (Mysql port)

En HeidiSQL, por ejemplo, para crear una base de datos:

  • Unnamed-1 (clic derecho) > Database > Create new > Nombre_database (crear esta base de datos)
  • Crear una tabla: Nombre_database (clic d) > create table: Name: nombre_tabla
  • Crear un campo en la tabla:
    • Add: nombre_campo (tipo VARCHAR)
    • Clic derecho en nombre_campo > create new index > primary (para hacer que dicho campo sea nuestra primary key)
  • Luego podemos ingresar datos a nuestro campo:
    • ...



Databases Authentication and Authorization

Authentication is a mechanism that determines whether a user is, who he or she claims to be.

MySQL: Create User and give a password

Create a new user called test and give a password of secret:

CREATE USER 'test'@'localhost' IDENTIFIED BY 'secret';

Las comillas en el user y el host no son obligatorias, por lo menos no en MySQL.


Para listar los usuarios:

SELECT USER FROM mysql.user;

Note: MariaDB /MySQL stores global privileges in the mysql.user system table.


Understand Authorization by granting and revoking of privileges Access control (related to authorization):

Authorization: The granting of a right or privilege, which enables a subject to legitimately have access to a system or a system’s object.

Grant some privileges to your new user. GRANT specifies account privileges:

GRANT SELECT, INSERT, UPDATE ON books.authors TO 'test'@'localhost';

We Grant the privileges to our books database to the authors table.

En este caso, el usuario 'test' es autorizado a SELECT, INSERT and UPDATE sólo en la la tabla 'authors' de la database 'books'. En este caso 'test' ni siquiera podrá ver las otras databases o tables de 'books'. La única database que 'test' puede ver (además de books) es 'information_schema'

Note: INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several readonly tables.

If we want revoke INSERT on 'test' user:

REVOKE INSERT ON books.authors FROM 'test'@'localhost';

Oracle Database

C'est un système de gestion de base de données relationnelle (SGBDR)

Oracle SQL Developer

C'est un environnement de développement intégré (EDI) multi-plateforme, fourni gratuitement par Oracle Corporation et utilisant la technologie Java (Java Development Kit). C'est un outil graphique permettant d'interroger des bases de données Oracle à l'aide du langage SQL.