Links

1. Database design of student achievement management system - MySQL

Database design of student achievement management system - MySQL

2. Medical information management system database – MySQL

Medical information management system database – MySQL

3. Mail management database design – MySQL

Mail management database design – MySQL

4. SQL Server medical information management system database [English version - source code] – (Medical Management System Database)

SQL Server medical information management system database [English version - source code] – (Medical Management System Database)

5. SQL Server cinema database management system [English version - source code] – (Movie Theatre Management System Database)

SQL Server cinema database management system [English version - source code] – (movie theater management system database)

1, Demand analysis

1. Research significance

Because a large amount of information and data are frequently involved in the operation of various computer-aided systems, database management system is also an important part, even the core part. Realize data sharing. Data sharing includes that all users can access the data in the database at the same time, and also that users can use the database through interfaces in various ways, and provide data sharing to reduce data redundancy. Compared with the file system, the database realizes data sharing, which avoids users from establishing application files. It reduces a large amount of duplicate data, reduces data redundancy and maintains data consistency.

2. System functions

"Commodity management system" is a typical database development application, which adopts multi document interface and is composed of purchase information, sales information, commodity information, customer information, inventory information and other sections. The specific planning is as follows:

1. Commodity information section
   This section is mainly responsible for adding commodity information, modifying commodity information and deleting commodity information.
2. Purchase information section
   This section is mainly responsible for the purchase function and querying the purchase records.
3. Customer information section
   This section is mainly responsible for adding customer information, modifying customer information and deleting customer information.
4. Functional structure of commodity management system:
   

2, Database conceptual structure design (design E-R diagram)

3, Database logical structure design

Logical structure design can be specifically understood as transforming the E-R model designed in the conceptual design stage into a logical structure model that can be used by a specific database management system. At the same time, the transformed logical model needs to be analyzed according to the requirements and the relationship between tables in the database needs to be standardized.

1. "Customer information table" (client)
   

2. "goods information table"
   

3. "purchase information table"
   

4. "Sales information table"
   

5. "Inventory information table" (stock)

4, Database physical structure design

1. Create database

Use T-SQL language to create "goods_management" for "commodity management system". The database is required to be in the database folder of disk D, which consists of a master data file ("goods_management_m") with an initial size of 3MB and a file increment of 10% and an unlimited maximum capacity, a secondary data file ("goods_management_n") with an initial size of 2Mb and an unlimited maximum capacity, and a secondary data file ("goods_management_n") with an initial size of 10MB and a file increment of 5mb, Transaction log files with unlimited maximum capacity ("goods_management_l").
The database file path is D: \ respective names\

create database goods_management
on
(  name='goods_management_m',
   filename='d:\\goods_management_m.mdf',
   size=30mb,
   filegrowth=10%,
   maxsize=unlimited),

( name='goods_management_n',
  filename='d:\\goods_management_n.ndf',
  size=20mb,
  filegrowth=2mb,
  maxsize=unlimited)

log on
( name='goods_management_l',
  filename='d:\\goods_management_l.ldf',
  size=10mb,
  filegrowth=5mb,
  maxsize=unlimited)

go

2. Create tables and constraints

Create client customer information table

create table client
(
client_id char(10) not null,
client_name nchar(4) not null,
mobile char(11) not null,
home_address nvarchar(30) not null,
email varchar(30) check( email like '_%@_%._%'),
primary key(client_id),
unique(mobile)
)

Create goods product information table

create table goods
(  goods_id char(20) not null,
goods_barcodevarchar(20) not null,
goods_namenvarchar(20) not null,
Production_placenvarchar(30) not null,
primary key(goods_id))

5, Implementation of database

1. Addition of table data

-- For table client Add data
use goods_management
go 
insert into client(client_id,client_name,mobile,home_address,email)
values ('201863516','Indian aster','13797284133','Wuhan, Hubei','1501563221@qq.com')

-- For table goods Add data
go
insert goods(goods_id,goods_barcode,goods_name,Production_place)
values('us00100001','6936983800013','Pen','Wuhan, Hubei')
insert goods(goods_id,goods_barcode,goods_name,Production_place)
values('us00200001','6916987800021','pencil','Jingzhou, Hubei')

-- For table purchase Add data

go
insert purchase
values('0622000001','us00100001','2.5','50','125','2018-6-22')
insert purchase
values('0622000002','us00200001','1','30','30','2018-6-23')

-- For table sale Add data

go 
insert sale(sale_id,goods_id,client_id,sale_price,sale_sum,sale_money,sale_date)
values('0702000001','us00100001','201863516','2.5','28','70','2018-7-2')
insert sale(sale_id,goods_id,client_id,sale_price,sale_sum,sale_money,sale_date)
values('0703000002','us00100002','201863544','30','10','300','2018-7-3')

-- by stock Add data
go
insert stock
values('us00100001','63')
insert stock
values('us00200001','129')