Fill This Form To Receive Instant Help
Homework answers / question archive / Experiment 3: Data Integrity and Security Goal To practice how to define the data integrity
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 |
NO Action/restrict/cascade/set to null?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
(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:
User name: Wang Ming @ localhost; password: 123456
To view system current user: system function user ()