1. Database design of student achievement management system - MySQL
2. Medical information management system database – MySQL
3. Mail management database design – MySQL
4. 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)
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:
- Commodity information section
This section is mainly responsible for adding commodity information, modifying commodity information and deleting commodity information.
- Purchase information section
This section is mainly responsible for the purchase function and querying the purchase records.
- Customer information section
This section is mainly responsible for adding customer information, modifying customer information and deleting customer information.
- 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.
"Customer information table" (client)
"goods information table"
"purchase information table"
"Sales information table"
"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','firstname.lastname@example.org') -- 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')