Design of simple database application system in Experiment 6 of SQL Server Database Principle

SQL Server experiment series

Design and implementation of simple database application system in Experiment 6

The aim of the experiment was to observe the effect of the treatment on the quality of the patients
(1) Understand and use common steps of database design to design conceptual model (ER model) and relational data model to meet given requirements;
(2) The model can be optimized according to relevant guidelines.
(3) The database schema can be created on a mainstream DBMS, and the database schema can meet the basic business needs of users;

The experimental contents were as follows
Given the following topics, each group of students choose a topic for the corresponding design and implementation. The specific topics are as follows:
(1) The repair orders of an automobile maintenance department are shown in the following table:

(2) A science and technology management department intends to develop a scientific research project application and evaluation system. Some information involved is as follows:
Project: project number, project name, applicant, term, amount, project description.
Project category: category number, category name.
Expert: expert number, name, title, unit.
Among them: according to the project category, each declared project needs to be reviewed by several experts in the corresponding field; each expert only participates in the review of one type of project, and the review date and review opinions shall be recorded during the review.
(3) Part of the information involved in a student dormitory management system is as follows:
Student: student number, name, gender, major, class.
Dormitory: dormitory number, room phone number.
Administrator: employee number, name, contact number.
Among them: each dormitory can accommodate more than one student at the same time, each student is assigned only one dormitory; each dormitory designates one of the students to be the dormitory head; each administrator manages multiple dormitories at the same time, but each dormitory has only one administrator.

Experiment process: (selection: student dormitory management system)
Each group of students from the above topics to choose one of the corresponding design and implementation. The requirements are as follows:
(1) Starting from the simple semantics of topic selection and following the steps of database design, the following contents are completed respectively: conceptual design (ER model), giving the final global ER diagram to meet the requirements, requiring the model to be as simple as possible, eliminating unnecessary redundancy, and giving reasons or explanations.

  • Entity:

    • Student: the attributes include student number, name, gender, major and class
    • Dormitory: property has room number, room phone
    • Administrator: the attributes include employee number, name and contact number
  • Inter entity relationship:

    • A dormitory has more than one student accommodation, a student only allocated one dormitory
    • One administrator manages multiple dormitories, and one dormitory can only have one administrator
    • Each dormitory students, to choose a dormitory head

(2) Logical design (relational model), convert ER model into appropriate relational schema, and properly standardize (optional), design related integrity constraints.

Standardization definition
The first paradigm Ensure field atomicity (ensure that each column cannot be subdivided)
The second paradigm Non key fields must depend on key fields (a table describes one thing)
The third paradigm Eliminate transitive dependencies (all non key fields cannot have transitive dependencies)

(mode 1)

  • Student: (student number, name, gender, major, class, dormitory number)
    • Primary key: student number
    • Foreign key: bedroom number
  • Dormitory: (dormitory number, room telephone number, dormitory student number, employee number)
    • Primary key: dormitory number or student number
    • Foreign key: employee number, dormitory student number
  • Name and telephone number of employee
    • Primary key: employee number

(mode 2)

  • Student: (student number, name, gender, major, class)

    • Primary key: student number
  • Dormitory: (dormitory number, room phone number)

    • Key: bedroom number
  • Administrator: (employee number, name, contact number)

    • Primary key: employee number
  • Accommodation: (dormitory number, student number)

    • Primary key: student number
    • Foreign key: dormitory number, student number
  • Student management dormitory: (dormitory number, dormitory student number)

    • Primary key: dormitory number, dormitory student number
    • Foreign key: dormitory number, dormitory student number
  • Management: (dormitory number, employee number)

    • Key: bedroom number
    • Foreign key: dormitory number, employee number

(3) Physical design: create a database in a mainstream DBMS, plan appropriate indexes (including clustered index and non clustered index) according to business requirements, and design necessary user views (i.e. external mode) to complete relevant query or update requirements.

Mode 1
Create table

--Create employee table
CREATE TABLE employee(
	eno varchar(8) PRIMARY KEY,
	ename VARCHAR(8),
	etel varchar(12)
);

--Create student table
create table student(
	sno varchar(9) PRIMARY KEY,
	sname varchar(8),
	ssex varchar(4)  CHECK (ssex='male' or ssex='female'),
	sspecialty varchar(9),
	sclass varchar(9),
	dno varchar(8),
);
--Create dormitory table
CREATE TABLE dormitory(
	dno varchar(8) PRIMARY KEY,
	dtel varchar(12),
	sno varchar(9),
	eno varchar(8),
	CONSTRAINT fk_Student FOREIGN KEY (sno) REFERENCES student(sno),
	CONSTRAINT fk_Emplyee FOREIGN KEY (eno) REFERENCES employee(eno)

);

--In the student list dno Change to foreign key
ALTER TABLE student 
ADD CONSTRAINT fk_Dormitory FOREIGN KEY (dno) REFERENCES dormitory(dno)

Design the view and complete the query requirements

Method 2
Table building

--Student list
CREATE TABLE student
(
	sno varchar(10) PRIMARY KEY,
	sname varchar(8),
	ssex varchar(4) CHECK (ssex='male' or ssex='female'),
	sspecialty varchar(9),
	sclass varchar(8)
);
--Dorm 
CREATE TABLE dormitory(
	dno varchar(6) PRIMARY KEY,
	dtel varchar(12)
);
--Administrator table
CREATE TABLE manager
(
	mno varchar(8) primary key,
	mname varchar(8),
	mtel varchar(12)
);

--Accommodation list (dormitory number, student number)
CREATE TABLE stu_putup(
	dno varchar(6),
	sno varchar(10) primary key,
	CONSTRAINT fk_stu_putup_sno FOREIGN KEY (sno) REFERENCES Student(Sno) on delete cascade on update cascade,
	CONSTRAINT fk_stu_putup_dno FOREIGN KEY (dno) REFERENCES dormitory(dno) on delete cascade on update cascade
);

--Student management dormitory table (dormitory number, dormitory student number)
CREATE TABLE stu_manage_dor(
	dno varchar(6) primary key,
	head_sno varchar(10),
	CONSTRAINT fk_stu_mange_dor_Sno FOREIGN KEY (head_sno) REFERENCES Student(Sno) on delete cascade on update cascade,
	CONSTRAINT fk_stu_mange_dor_dno FOREIGN KEY (dno) REFERENCES dormitory(dno) on delete cascade on update cascade
);

--Management table (dormitory number, employee number)
CREATE TABLE manage_dor(
	dno varchar(6) primary key,
	mno varchar(8),
	CONSTRAINT fk_mange_dor_dno FOREIGN KEY (dno) REFERENCES dormitory(dno) on delete cascade on update cascade,
	CONSTRAINT fk_mange_dor_mno FOREIGN KEY (mno) REFERENCES manager(mno) on delete cascade on update cascade
);

Design view

Tags: Database SQL

Posted on Tue, 30 Jun 2020 00:50:15 -0400 by habanero25