Analysis of course design of SQL Server database

Topic: Energy Management fee system

Basic requirements for system functions:

(1) User basic information input: including the user's unit, department, name, contact telephone, address;

(2) Users of water, electricity, gas data input (data entry per month);

(3) Price management of water, electricity and gas

(4) Management of the work number

(5) Query, statistical results output.

Database requirements: At least the following data tables should be included in the database:

(1) User basic information table;

(2) water, electricity, gas unit price list and history record table, etc.

(3) Work number and permission list
1. The form information created according to the topic is as follows:
User Information table: Users (UNO,UNITDEPA,NAME,TEL,AD,PASSWD)

Water Price Standard table: Water (wid,price,begintime,endtime)

Electricity Price Standard table: Elec (eid,price,begintime,endtime)

Gas standard table: gases (gid,price,begintime,endtime)

User water Consumption chart: Water_consume (uno,time,wid,num,state)

Similar to electricity and gas consumption tables
User Consumption chart: Elec_consume (uno,time,eid,num,state)
User gas Consumption chart: Gas_consume (uno,time,gid,num,state)
Employee Information Sheet: Employee (WNO,PASSWD)

Permission table: Rights (Rightid,comment,function)

Employee Permissions Table: Staffrignt (Wno,rightid)

Analysis:

• Initial status information is automatically generated in the energy consumption record when the user registers

• The Permissions table design contains: Permission number, description and function, when adding permissions, only need to add a row, employees increase permissions only need to add a row

• When the water bill changes the standard, if there is no price to modify in the Water standard table, add a tuple, and attach the start and end time, if present, only need to modify the start and end time

2.e-r Diagram Drawing
E-r diagrams are also known as instances-contact diagrams, which contain instances, attributes, and links between
A flowchart is a process diagram that is used to represent the entire process of a system, the logical process

• E-r Chart

• Flow chart

Analysis of course design of SQL Server database