Homework answers / question archive / Experiment 3: Data Integrity and Security Goal To practice how to define the data integrity

Experiment 3: Data Integrity and Security Goal To practice how to define the data integrity

Computer Science

Share With

---

1. Experiment 3: Data Integrity and Security
   1. Goal

1. To practice how to define the data integrity.
2. To practice how to create users
3. To practice how to grant/revoke privileges of databases and tables.

 

1. 2. Content

Database name?Student? including 3 tables?

S (SNO, SNAME, SGENDER, SBIRTH, SDEPT, SAGE)

C (CNO, CNAME, CPNO, CREDIT)

SC (SNO, CNO, GRADE)

Student information table S consists of student number (SNO), name (SNAME), gender (SGENDER), date of birth (SBIRTH), deparatment(SDEPT), age (SAGE).

Course information table C consists of course number (CNO), course name (CNAME), prerequisite course number (CPNO) and credit.

SC is composed of SNO, CNO and GRADE, it indicates that a student has taken a course with grade.

Table S                                             Table SC

SNO	SNAME	SGENDER	SBIRTH	SDEPT		SNO	CNO	GRADE
2001	Yong Li	male	2000/01/01	MA		2001	1	92
2002	Liu Chen	female	2001/02/01	IS		2001	2	85
2003	Wang Min	female	1999/10/01	CS		2001	3	90
2004	Zhang Li	male	2001/06/01	IS		2002	2	78
						2002	3	84
						2003	6	91

 

 

	Table C
CNO	CNAME	CPNO	CREDIT
1	Database	2	3
2	Math		5
3	Information system	1	2
4	Operating system	5	3
5	Data structure	6	3
6	C languange		2

 

1. Add the following constraint and index with GUI(for university database)?10 points?.
   1. 1. Not null?add the not null constraint to S(SBITH).
      2. Primary key?set the SNO as the primary key.
      3. Unique constraint?add unique constraint for the primary key of SNAME, the constraint name is uk_Sname.
      4. Default constraint?add the default value to S(SGENDER), the default value is “male”.

 

2. Add forgein key constraints to table SC in the database Student with GUI,  set SNO(foreign ke name is: sc_fk_sid) as a foreign key referencing table S, and set CNO as another foreign key referencing table C , name it with sc_fk_ cno. Try and validate different strategies in violation of the foreign key constraints :

 NO Action/restrict/cascade/set to null?10 points?

 

3. Drop the three tables in database Student, and create some tables through SQL statements with the following constraints.(10 points?

• Table S?same to the constraints set in question 1(1)?
• Table C?set  CPNO as a foreign key?referencing table c itself with the attribute CNO.
• Table SC?set the foreign key constraints same to question2, and set the valid range of attribute GRADE with[0,100]. In addition, add one attribute ID to table SC, and set it as a primary key, and it can increase automaticlly. When a new tuple is inserted to the table, its(ID) value will increase by +1.

 

4. Add or remove the following integrity constraints with SQL language. (10 points)

(1) Add constraint: SGENDER ’s value in table S can only be "male" or "female".

(2) Delete the SGENDER value constraint created in table S.

(3) Remove the foreign key constraints from the SC table.

(4) Add a new column TNAME (indicating the name of the student's tutor) in the student table S, and require that the tutor name must be all uppercase or lowercase letters, and the length cannot be less than 8 characters (Hint: You can use the Length() function and regular expression to CHECK your character string).

 

Try to finish the following requirements by connecting to the local database server

 

5. Create and authorize new users in GUI. (20 points)

(1) Create two users who can access the current student database: Wang Ming and Li Yong.

(2) Complete the following authorizing:

? User Wang Ming has the priviliges to select and insert all tables.

? User Li Yong has the priviliges of select, insert, delete, update and create on the database.

 

2. Use SQL statement to authorize and withdraw permissions and verify permissions. (40 points)

Requirements: create relevant users and specified database tables, complete authorization and authority verification for each question, and then withdraw the authority and verify the authority.

(1) User Wang Ming has query privilege on two tables.

(2) User Li Yong has insert and delete privileges on the two tables.

(3) Each instructor only has the right to query his own record.

(4) User Liu Xing has query privilege on employee table and update privilige on salary field.

(5) User Zhang Xin has the right to modify the structure of the two tables.

(6) User Zhou Ping has all permissions on the two tables, and has the authority to authorize other users.

(7) User Yang Lan has the privilige to query the maximum wage, minimum wage and average wage from each department employee, but he cannot view the salary of each person.

Hint:

• Create user SQL statment: create user 'Wang Ming' @'localhost 'identified by' 123456 ';( in command line, ' is the quotation mark)

User name: Wang Ming @ localhost; password: 123456

• You can use the show grants for username ?including the location: for example @localhost?to view the privileges list of each user.
• Create an appropriate view for some questions in this part.

To view system current user: system function user ()