1 the task
You as a "construction project supervision information system" project team of programmers, as required, please complete:
? Create a database;
? Create a data table;
? The operation of the data,
2 ask
"System permissions management submodule of e-r diagram as shown in figure 2.1, the logical data model as shown in figure 2.2, the physical data model as shown in figure 2.3, the data table definition field names are shown in table 2.3, complete database creation, please click the following design data table creation and data manipulation tasks:
Figure 2.1 e-r diagram
Figure 2.2 the logical data model
Figure 2.3 physical data prototype
Table 2.3 the field name definition table
The field name field shows that the field name field description
Func_id function id User_passwd user password
Func_name function name Dept_id department
Func_role_id functional role id Telephone contact number
Func_role_name character name Address contact Address
User_id user id Handphone number phone
User_name username Usb_no combination lock,
Reserve note
3 database operations
3.1 create a database
Create the database ConstructionDB,
3.2 create a data table
According to the figure 2.2 and table 2.3, create data table T_user, T_func_item, T_func_role_def,
The relationship between 3.3 to create data table
According to the physical prototype data, create data relational tables,
3.4 data operations
Use SQL statements to complete the following:
? In table T_user insert data: "id01, Andy lau, 123, the supply, 5678900, in changsha, hunan province, 13899005678, ldh123, admin";
? Query the subordinate departments to "supply" of the basic information of the operator;
? Query the name to "Andy lau" operator has what function privileges;
? Query the function of "bid responsible role have;
? Create a view query operator's name, password and subordinate departments;
Create a stored procedure, the functions of the query operators permissions
CodePudding user response:
Ask you, I am a little stupid, probably is not difficult, but has just started, is not very will, thank youCodePudding user response:
# create database ConstructionDB
The CREATE DATABASE ConstructionDB;
# use ConstructionDB
Use ConstructionDB;
# to create basic information table data table operator
The CREATE TABLE T_user (
User_id CHAR (4) PRIMARY KEY COMMENT 'user ID',
User_name CHAR (16) the COMMENT 'user name',
User_password CHAR (16) the COMMENT 'user passwords,
Dept_id CHAR (3) the COMMENT 'departments'
Telephone VARCHAR (16) the COMMENT 'contact number',
Address VARCHAR (32) the COMMENT 'contact Address'
Handphone VARCHAR (16) the COMMENT 'phone number',
Usb_no VARCHAR (64) the COMMENT 'number combination lock,
Reserv VARCHAR (64) the COMMENT 'remarks'
);
# to create primary function definition table
The CREATE TABLE T_func_item (
Func_id CHAR (3) PRIMARY KEY COMMENT 'function ID',
Func_name VARCHAR (32) the COMMENT 'function name,
Reserv VARCHAR (64) the COMMENT 'remarks'
);
# to create functional role definition table
The CREATE TABLE T_func_role_def (
Func_role_id CHAR (3) PRIMARY KEY COMMENT 'function ID',
Func_role_name VARCHAR (32) the COMMENT 'function name,
Reserv VARCHAR (64) the COMMENT 'remarks'
);
Create a relational table # 1
The CREATE TABLE Realationship_1 (
Func_id CHAR (3) the COMMENT 'function ID',
Func_role_id CHAR (3) the COMMENT 'functional role ID',
PRIMARY KEY (Func_id Func_role_id),
The CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_I FOREIGN KEY (Func_id) REFERENCES T_func_item (Func_id),
The CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_R FOREIGN KEY (Func_id) REFERENCES T_func_role_def (Func_role_id)
);
# to create a relational table 2
The CREATE TABLE Realationship_2 (
Func_role_id CHAR (3) the COMMENT 'functional role ID',
User_id CHAR (4) the COMMENT 'user ID',
PRIMARY KEY (Func_role_id, User_id),
The CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_R1 FOREIGN KEY (Func_role_id) REFERENCES T_func_role_def (Func_role_id),
The CONSTRAINT FK_RELATION_RELATIONS_T_USER FOREIGN KEY (User_id) REFERENCES T_user (User_id)
);
# insert data
INSERT into T_user values
(' 01 ', 'Andy lau', '123', 'supply', '5678900', 'hunan changsha', '13899005678', 'ldh123', 'admin');
# query the subordinate departments to "supply" of the basic information of the operator;
SELECT
*
The FROM
T_user
WHERE
='supply' Dept_id;
# query the name to "Andy lau" operator has what function privileges;
SELECT
F.F unc_name 'features'
The FROM
T_user u Realationship_2 r2, Realationship_1 r1, T_func_item f
WHERE
Dept_id='Andy lau'
AND
U.U ser_id=r2. User_id
AND
R2. Func_role_id=r1. Func_role_id
AND
R1. Func_id=f.F unc_id;
# query out "bid responsible" roles have function;
SELECT
Fi. Func_name 'features'
The FROM
T_user u,
Realationship_2 r2,
Realationship_1 r1,
T_func_item fi,
T_func_role_def fr
WHERE
U.U ser_id=r2. User_id
AND
R2. Func_role_id=r1. Func_role_id
AND
R1. Func_id=fi. Func_id
AND
R2. Func_role_id=fr. Func_role_id
AND
Fr. Func_role_name='bid responsible;
# to create view query operator's name, password and subordinate departments;
The CREATE VIEW view_T_user AS
SELECT
User_name 'name', User_password 'password', Dept_id 'departments'
The FROM
T_user;
# to create the stored procedure, the functions of the query operators access
Delimiter $$
CREATE PROCEDURE check_func (IN user_name CHAR (16))
The BEGIN
SELECT
F.F unc_name 'features'
The FROM
T_user u Realationship_2 r2, Realationship_1 r1, T_func_item f
WHERE
Dept_id=input_name
AND
U.U ser_id=r2. User_id
AND
R2. Func_role_id=r1. Func_role_id
AND
R1. Func_id=f.F unc_id;
END $$
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull