Database System concept 6-data type, authorization, etc.

?First, consistency
Consistency includes not NULL, unique, check
A) NOT NULL
Name varchar (NOT NULL)
b) Unique
If A1, A2 ... And so make up the candidate key, you can use unique (A1, A2 ...) To guarantee their uniqueness, but these fields can still be empty, and null values are not equal to any of the values.
c) Check
Limit the value of semester:
Check (semester in (' Fall ', ' Winter ', ' Spring ', ' Summer ')
d) Referential integrity referential integrity
If Course.dept_name references the department table as a foreign key, to ensure that the course.dept_name values are present in department.dept_name, add referential integrity constraints as:
e) foreign key (dept name) References Department

ii. data types and schemas
A) Date and time
The time-related types specified by the SQL standard are:
Date ' 2001-04-25 '
Time ' 09:30:00 '
Timestamp ' 2001-04-25 10:29:01.45 '
Dates in the form of strings can be converted using cast E as T, or extract year/month/day/hour/minute/second from D can be used to extract data from month to day separately;
There are also Current_day, current_timestamp (including time zones), Localtimestamp (local time without time zone);
The interval type represents the difference in time
b) Default value
CREATE TABLE Student
(ID varchar (5),
Name varchar () is not NULL,
Dept Name varchar (20),
tot_cred numeric (3,0) default 0,
Primary KEY (ID));
The default value for tot_cred set here is 0.
c) Create an index
The CREATE Index StudentID index on student (ID) indicates that an index named StudentID was created, and some database products further differentiate between clustered indexes (clustered) and nonclustered indexes (nonclustered)
d) Large object type Large-object type
If you want to store data such as sounds, images, and so on, the amount of data may be KB or even MB, GB, which provides two large object types Clob (character large object) and BLOBs (binary ...) for this SQL. There are differences in the implementation of different databases, and it is not recommended to use these types in real-world use, but to keep the data in the file system and store it in the database.
e) User-defined type
Allows you to customize data types based on existing types, such as:
Create type Dollars as numeric (12,2);
Create type Pounds as numeric (12,2);
Custom types dollars and pounds are numeric (12,2) types, but are considered to be different data types on the business.
There is also a way to define:
Create domain Ddollars as numeric (12,2) not null;
The subtle difference between type and domain is that domain can add constraints such as NOT NULL at the same time, and domain is not a fully strong type, as long as the value is compatible, it can be assigned to the type defined by the domain, and type does not.
e) The extension of the Create table
CREATE TABLE temp instructor like instructor; Creates a form with the same structure as Sinstructor
When writing SQL, temporary tables are sometimes created and stored in data, which can be simplified by:
CREATE TABLE T1 AS
(SELECT *
From instructor
where dept name= ' Music ')
with data;
The T1 table structure is the same as the query result set, and if you remove with data, only the schema is created and no data is inserted.

Iii. delegation of authority
Permission control can control data manipulation, schema update, etc. for users or roles.
A) assignment, revocation of authorization
The syntax for assigning permissions is:
Grant <privilege list>
On <relation name or view name>
To <user/role list>;
Privilege list includes SELECT, INSERT, UPDATE, delete
For update, you can set certain properties that are allowed to be updated:
Grant Update (budget) on department to Amit, Satoshi;
Similarly, the revoke authorization syntax is:
Revoke <privilege list>
On <relation name or view name>
To <user/role list>;
b) Role
Role-based permissions control is not a patent for SQL, and many shared applications use this type of authorization.
Create role Instructor;
Grant Select on takes to instructor;
Grant Dean to Amit;
The preceding statement creates the role instructor, allocates select from takes permissions, and then Amit into the instructor role. Before Amit executes a query, SQL controls it according to the permissions that it owns.
c) Authorization of the schema
Because foreign keys affect subsequent updates, deletions, and so on, it is necessary to do permission control for foreign key creation:
Grant references (Dept name) on department to Mariano;


Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan

Database System concept 6-data type, authorization, etc.