Relational Database 1 | Installation of Postgres and PgAdmin, An Intro to DBMS, ER Diagrams, and Structure Query Language (SQL)
- Installation of Postgres and PgAdmin
(1) Installation and Configuration of PostgreSQL
In macOS, we can run the following code to install the PostgreSQL,
$ brew install postgresql
Then we have to initial the database in a given path, for example, we will use the path of /usr/local/var/postgres here,
$ initdb /usr/local/var/postgres
Ignore the Bala Bala … disabled … Bala Bala … other than … Bala Bala … output, this won’t affect our database performance.
We should also create a user named postgres after this procedure, by,
$ /usr/local/opt/postgres/bin/createuser -s postgres
Okay, now we’ve done on the installation and initialization.
(2) Setup the PostgreSQL Server
To use our PC as the server, we can set up the database on localhost port 5432 by,
$ postgres -D /usr/local/var/postgres
Don’t close this window because we have to keep the server open.
(3) Create a PostgreSQL Client
Then we open another window of terminal and we then type in,
$ psql -d admin
because we haven’t created any database named admin, the terminal will then send back an error message,
psql: error: could not connect to server: FATAL: database "admin" does not exist
And also, we are going to get an error message in the server terminal,
CST [...] FATAL: database "admin" does not exist
We can then close the terminal windows.
(4) pgAdmin: An User-Friendly UI of PostgreSQL
Bored about the command line? So do I! Now let’s try something new now. We have known that localhost:5432 is used for our PostgreSQL services. So how about we use an html-based user interfacer to show us the result of our SQL commands? Sounds like a good idea, right? The answer to this is we can have pgAdmin software.
Firstly, we go to the website of pgAdmin and download the latest version. In this case, we choose to download the macOS version pgAdmin 4 v4.26 (released Sept. 17, 2020). After downloading it, we then open this file with a double click, and then probably, we have to wait a second for it to initialize. After that, we can add (copy or move) it to the application folder so that it will then be software on our computer.
To open the pgAdmin, we don’t have to use the terminal anymore. Just one click on the pgAdmin, it will automatically create a server for us on a leisure port.
Then you are going to type in the password and make sure that you can remember this password (you will be in big trouble if you forget this) and press ok to continue.
Then we press the Add New Server button (like a sandwich) and we can get this window:
So the first step is that we are going to assign the name of this server as localserver,
then we press the connection button and type in 127.0.0.1 and the port 5432,
Then we should also write the username and the password,
Password: <your password>
Select “save password?” to make life easier for us. If things are not messed up, you are expected to get the following page. Congratulations if you did it all right!
2. An Introductions of the Database Management System (DBMS)
(1) Features of the Database Management System
- Data Independence: the data in the DBMS is multi-layered (physical/logical/data) and data is independent between the different layers
- Data Abstraction: the DBMS allows us to see the hidden features of the data
- Efficient Access: DBMS is efficient to store and retrieve data
- Follow Integrity: DBMS can set rules for valid data
- Security: allow different permissions for different users
- Data Administration: centralize the admin of the data
- Allow Concurrent Access: with multi-users logging in simultaneously
- Backup: DBMS always allow crash recovery
- Reduced Development Time
(2) The Definition of the Relation (aka. Record)
In the field of the relational database, a data table is called a relation. It is also called a record.
(3) The Definition of the Schema
The description we used to describe the data in a relation is called a schema of this relation. For example, a relation of student ID and the student name should include the sid (string, Not NULL, UNIQUE) and the name (string, Not NULL).
(4) The Definition of Tuples
Each line in a relation is called a tuple. Thus, each relation has a collection of tuples.
(5) The Relation between Relations
Because different relations can also have relations, we call this the relation between relations. (i.e. the department relation and the faculty relation can have a relation)
(6) Relational Database Design Steps
- Step #1: Requirement Analysis — what to put in the database?
- Step #2: Conceptual Database Design — develop an ER diagram
- Step #3: Logical Database Design — convert the ER diagram to the database schema
- Step #4: Schema Refinement — identify potential problems and refine
- Step #5: Physical DB Design — make sure the database fits the performance
- Step #6: Security Design — define the role and the accessibility of the role
3. Entity-Relationship (ER) Model
(1) The Definition of the Entity
An entity is an object that is distinguishable from the other objects and it can be described using a set of attributes.
(2) The Definition of the Primary Key
Super Key − A set of attributes (one or more) that collectively identifies an entity in an entity set.
Candidate Key − A minimal super key is called a candidate key. An entity set may have more than one candidate key.
Primary Key − A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set. That is to say, the primary key is one of the minimal sets of attributes whose values uniquely identify an entity in the set.
Unique Key − The primary key is one of the minimal sets of attributes whose values uniquely identify an entity in the set. The difference between the primary key and the unique key is that: (a) we can have several unique keys if a relation but we can have only one primary key; (b) the unique key can contain NULL values whereas the primary key can only have NOT NULL values.
Foreign Key − The foreign key is the information stored in a relation linked to the information stored in another relation.
(3) The Definition of the Attributes
The attributes of an entity is the properties of this entity. Each attribute of an entity is assigned by a value.
(4) Entity: Examples
For example, an entity of the employee should be,
- eid: string, NOT NULL, UNIQUE (Primary Key)
- name: string, NOT NULL
- title: string
- SSN: string, UNIQUE
Another example should be the department entity,
- did: string, NOT NULL, UNIQUE (Primary Key)
- name: string, NOT NULL, UNIQUE
Note that department name can also be a primary key and, actually, we can arbitrarily choose a primary key between did and name.
(5) The Definition of the Relationships
The relationship is an association between two or more entities. A relationship can have descriptive attributes.
│ - uid: string, NOT NULL, UNIQUE (Primary Key)
│ - name: string, NOT NULL
- timestamp: string
- pid: string, NOT NULL, UNIQUE (Primary Key)
- tag: string
- location: string
(6) The Definition of the Constraints
The key constraints are used to restrict an one-to-one, an one-to-many, a many-to-one, or a many-to-many relationship (going to explain later). It is always being represented by arrows.
(7) The Definition of Total Participation
Each entity is involved in the relationship.
(8) The Definition of Partial Participation
Not all entities are involved in the relationship.
3. ER Diagrams
Entities are represented by rectangles.
Entities are represented by ellipses.
Relationships are represented by diamonds.
Constraints are represented by arrows with 1 and N.
(5) Total Participation and Partial Participation
Total participation is represented by double lines, while partial participation is represented by a single line. For example, suppose we have all the professors participate in the program and teach courses. Some courses have more than one teacher, but each professor can only teach one course.
4. Structure Query Language (SQL)
(1) Create a Database
In order to create a new database, first of all, we right-click on the postgres in the databases tag and choose Query Tool,
Then in the Query Editor, we write (replace the <dbname> with your database name),
CREATE DATABASE <dbname>;
Then on the top of the editor, we press the triangular sign to run the code,
then we are going to have the output:
CREATE DATABASE Query returned successfully in 262 msec.
So where is this new database? To see this new database, we have to refresh the databases tag, by right click on it and choose Refresh,
Now let’s do something to our newly created database.
(2) CRUD Operation
CRUD operations! Sounds scary for the first time right? However, they are silly if you know the whole name of them. Basically, the CRUD operations are:
- Create / Insert operation
- Read operation
- Update operation
- Delete operation
(3) Create a Table
First of all, let’s create a table, we go to the Query Tool of our new database and then write,
CREATE TABLE <tablename> ( <att1name> <att1type> <att1constraint>,
<att2name> <att2type> <att2constraint>,
<attNname> <attNtype> <attNconstraint>,
- <tablename>: the name of the table
- <att?name>: the attribute name
- <att?type>: the type of attribute
In SQL, we can use types as,
(1) Boolean type: boolean
(2) Character Types: char(n), varchar(n), text
(3) Numeric Types: integer, serial, real
(4) Date/Time Types: timestamp , date, time, interval
- <att?constraint>: the constraint of the attribute (option)
In SQL, we can use constraints as,
(1) CHECK <coonditions>: specify an expression which newly updated rows must follow
(2) NULL/NOT NULL: null values is allowed or not allowed
(3) DEFAULT <expression>: set the default value for column
Now let’s create a table called employees,
Also, we can create a department table by,
Finally, we can create a Works_In table by,
(4) Insert an Instance
We can use the following code to insert a line,
INSERT INTO <tablename>
VALUES <value1, value2, value3, ...>;
We have to make sure that the values follow the rules we have set. For example, we run the following code to give instances to the tables,
INSERT INTO employees
VALUES (1, 'Diane', 'manager');
INSERT INTO employees
VALUES (2, 'Abigail', 'engineer');
We can also add two values at the same time, for example,
INSERT INTO departments
VALUES (1, 'Data Science'), (2, 'Human Resources');
Suppose we know that Diane works at the Data Science department and Abigail works at HR, so what we can do is to run,
INSERT INTO works_in
VALUES (1, 1), (2, 2);
To see the change, we can right-click on the table we would like to show and choose All Rows.
(5) Load Data from a Given File
Suppose we have a .csv file that is located in the path <path> that we would like to load to a given table in the database, what we can do is,
we actually have this data file and you can have a try of the following code,
(6) Change an Existing Data
SET <att1name> = <val_1>
<att2name> = <val_2>
(7) Change the Structure of the Table
ALTER TABLE <tablename>
the action can be,
ADD COLUMN <attname> <datatype> <constraint>;DROP COLUMN <attname>;RENAME COLUMN <attname>
TO <new_attname>;ALTER COLUMN <attname>
SET NOT NULL;ALTER COLUMN <attname>
DROP NOT NULL;RENAME TO <new_attname>;
(8) Remove Tuples from a Table By Given Constraints
DELETE FROM <tablename>
(9) Delete a Table From a Database (if we know it exists)
DROP TABLE <tablename>;
(10) Delete a Table From a Database (if we are not sure whether it exists)
DROP TABLE IF EXISTS <tablename>;
(11) Remove the table and its dependent foreign key constraints in a referencing table
DROP TABLE IF EXISTS <tablename> CASCADE;
(12) Reject the removal if there is any object depends on the table
DROP TABLE IF EXISTS <tablename> RESTRICT;
Note that we don’t actually have to add this RESTRICT query because it is the default option.
(13) Retrieve or Show Data from a Table
SELECT <att1name>, <att2name>, ...
(14) Retain All the Data From a Table