Experimental one database model design and establishment
Purpose:
1. Understand basic knowledge in database design concept: such as entities, attributes, contacts ...
2. Can solve the common problem in the design: If you use an entity or use attribute?
3. Make students can independently complete the design of the database mode, including the structural (attribute names, types, constraints, and keywords, etc.) and tables of tables and their connection, and set up database tables on selected DBMS.
4. Familiar with the use of database modeling tools (PowerDesign)
Experimental requirements:
1. Seriously read business needs, make E-R pictures, and optimize the design of the database structure according to the design principles
2. Create a database on the selected DBMS, compare the advantages and disadvantages of automatically generating databases with manually entering SQL establishing databases and modeling tools
Experimental environment and time schedule:
Office, PowerDesign7.5
2. SQL Server7.0
3. When studying: 3 hours
Experimental content:
Assumptions
A college has a basic entity set: department, teachers, students and courses.
They have their own properties: department: department number, class name, location course: course number, course name, start student: student number, student name, gender, address teacher: employee, teacher name, office
There is a selection setting
Every department has a department, there are many teachers; a teacher is only in one department; each department has a multi-course course; each course has been taught by many teachers;
A teacher door teaches a multi-do-doctors a student to choose a multi-do-door course in different systems.
The existence link is: 1 pair 1: Department and department (department director is a teacher) 1: Department and Teachers, Department and Course
Many: Students and Course, Teachers and Course
Experimental tasks and steps
l Draw the E-R map of the above relationship (Word)
l Write the relational database model of the college database and give the instructions (modeling tool PowerDesign builds Phisical Data Model)
Experimental report: (content and requirements)
1. E-r Figure (.doc)
2. Database model (.pdm file)
3. Submit an experimental report in writing in writing and submitted in strict accordance with the format of Annex 1 and Annex 2.
Experiment 2 SQLServer Database Basic Management and Data Definition Language (DDL)
Purpose:
1. Master the start of the database server, log in
2. Understand the use of Enterprise Manager as a database operation
3. Master the data definition language and data type of SQL Server
4. Understand the use of tools (Enterprise Manager) as data definitions and use SQL statement data definitions
5. Learn more constraints such as setting keycode constraints, external volume constraints, non-empty constraints, unique constraints, and value constraints.
Experimental requirements:
1. Start, log in to the SQL Server database server
2. Create and modify and delete databases, users, basic tables, indexes using Enterprise Manager
3. Create and modify and delete users, basic tables, indexes directly using the SQL statement (Query Analyzer)
Experimental environment and time schedule:
SQL Server7.0
2. Time: 3 hours
Experimental content:
1. Start SQL Server Database Server 2, log in to SQL Server Database Server
3. Use Enterprise Manager to create your local application database (name UserDb) and make modifications
4, use Enterprise Manager to create your personal English name account (Login) and users (User)
On the basis of experiment, we conduct some basic data definitions.
Data Dictionary
table
Name
Chinese name
TBLCOLLEGE
Department
TBLCOURSE
course
TBLselCourse
Choose a course
TBLstudent
student
TBLTEacher
teacher
TBLCOLLEGE
Field name
type of data
Can it be empty?
IS PK
IS fk
Chinese name
Collegeid
int
NOT NULL
YES
NO
Department ID
COLEGENAME
VARCHAR (100)
NULL
NO
NO
Department name
Address
VARCHAR (200)
NULL
NO
NO
position
Teachernum
VARCHAR (20)
NULL
NO
YES
Director ID
Course TBLCOURSE
Field name
type of data
Can it be empty?
IS PK
IS fk
Chinese name
Coursenum
VARCHAR (20)
NOT NULL
YES
NO
Course No
CourseName
VARCHAR (100)
NOT NULL
NO
NO
Course Title
Term
int
NULL
NO
NO
Semester
Collegeid
int
NULL
NO
NO
Department ID
Teachernum
VARCHAR (20)
NOT NULL
NO
NO
Teacher number
Selection TBLSELCOURSE
Field name
type of data
Can it be empty?
IS PK
IS fk
Chinese name
StudTnnum
VARCHAR (20)
NOT NULL
YES
YES
Student number
Coursenum
VARCHAR (20)
NOT NULL
YES
YES
Course No
SELDATE
Datetime
NULL
NO
NO
Date of selection
Practicemark
Decimal (4, 2)
NULL
NO
NO
Practice achievement
Testmark
Decimal (4, 2)
NULL
NO
NO
Exam score
Student TBLstudent
Field name
type of data
Can it be empty?
IS PK
IS fk
Chinese name
StudTnnum
VARCHAR (20)
NOT NULL
YES
NO
student ID
StudentName
VARCHAR (20)
NULL
NO
NO
Name
SEX
Char (2)
NULL
NO
NO
gender
Address
VARCHAR (200)
NULL
NO
NO
address
Teacher TBLTEacher
Field name
type of data
Can it be empty?
IS PK
IS fk
Chinese name
Teachernum
VARCHAR (20)
NOT NULL
YES
NO
Teacher number
Teachername
VARCHAR (20)
NULL
NO
NO
Teacher name
Office
VARCHAR (20)
NULL
NO
NO
Office number
Collegeid
Int
NOT NULL
NO
YES
Department ID
5. Establish an Enterprise Manager and SQL statement (Query Analyzer, respectively, the above basic table (pay attention to primary key, foreign key, constraint, etc.)
6. Establish an index (free selection field) experiment report for each table with Enterprise Manager and SQL statement (Query Analyzer): (Content and Requirements)
1. Enterprise Manager use report
2.5, 6 two experiments SQL education (.doc)
3. Submit an experimental report in writing in writing and submitted in strict accordance with the format of Annex 1 and Annex 2.
Insert and updates of data in the experiment three database
Purpose:
1. Master basic insertion, update, delete SQL statements
2. Understand the role of the keycode constraints, external volume constraints, non-empty constraints, unique constraints, and restrictions, etc., and modifies the modification of the input data and data.
3. Exercise index, the establishment and deletion of the view
Experimental requirements:
1. Skilled insert, update, delete SQL statements
2. Effects of key code constraints, outer code constraints, non-empty constraints, unique constraints, value constraints, and other constraints in input data and data.
3. Learn to index, the establishment and deletion of the view
Experimental environment and time schedule:
SQL Server7.0
2. When studying: 2 hours
Experimental content:
On the basis of experiment 2, we conduct some basic data insert, update, delete
1, insert data
Department
Collegeid
COLEGENAME
Address
Teachernum
1
School of Computer and Communication
No. 184, Shan South Road
11001
2
Chemical Engineering College
Seeking Dechang No. 24
12001
3
Department of Mathematics
No. 35, Beijing Road
Since the type of CollegeID is Identity, the system will automatically numbered; for Teachernum, it corresponds to a data in the teacher table, which can be NULL due to non-strong constraints.
teacher
Teachernum
Teachername
Office
Collegeid
11001
Songjiang
Computer building 302
1
11002
Wuyue
Computer building 303
1
12001
Wang San
Chemical building 101
2
13001
Zhang Wu
3
Collegeid corresponds to a record in the system form, because it is a mandatory constraint relationship, so it can't be empty
course
Coursenum
CourseName
Term
Collegeid
Teachernum
1110001
Computer Architecture
Seduce
1
11002
1330002
Combinatorial Mathematics
6
13001
student:
StudTnnum
StudentName
SEX
Address
111001
Six Small Lingtong
male
507 507
111002
Green flower
Female
For field sex, we create rules only allow input characters 'men' or 'female'
Choose a course
Studentnum
Coursenum
SELDATE
Practicemark
Testmark
111001
1110001
2003-03-01
85
90
111001
1330002
2002-09-01
75
75
111002
1110001
2003-03-01
75
The selection form is defined in many more-to-many relationships between students and courses. It is a table that has been evolved. Its field studentnum, courtenum is a forced constraint field, which corresponds to a record in TBLstudent and Tblcourse, which cannot be empty. Colon with candidate key
2, update data
Lison Department of Lithics is Zhang Wu
l Modify the combined mathematical examination score of Cuihua is 90 points.
3, establish an index
l Establish index of student name (TBLSTUDENT) to establish indexes for the course table (TBLCOURSE) to the candidate keyword
4. Create a view
l Establish a view to show all the practices and test scores of all students, request fields
(StudientName, CourseName, Practicemark, Testmark)
l Establish a view to show the average of all subject test scores, request fields (coursenuM, courseename, avgmark)
Experimental report: (content and requirements)
1. SQL script of experimental content (.doc)
2. Submit an experimental report in writing in writing and submitted in strict accordance with the format of Annex 1 and Annex 2.
Inquiry in the experimental four database
Purpose:
1. Master basic queries, nested subqueries and connection queries
2. Partial reserved character of the database
3. Learn part of the statistical function.
Experimental requirements:
1. Skilled in the basic query, nested subqueries and connection queries
2. Experience the excessive and mutual transformations of various queries, experience the implementation process of various queries, and lay a good foundation for simple integrated applications.
Experimental environment and time schedule:
1, SQL Server7.0
2, learn: 3 hours
Experimental content:
First we review the first two experiments, simply create a database book; then practice some basic queries, nested subqueries and connection queries, and learn some of the statistics functions
1, establish a database
The picture map of the book is as follows:
Entity and attributes
Book: (Number, Category, Title, Publishing House, Author, Price, Total, inventory)
Book card: (card number, card number name, unit, card type)
Note: Number, card number is the primary key; stock (stock) Check constraint total> = stock> = 0); card type (TYPE) must be the following characters ('t', 'g', 'u', 'o')
Contact set
Branch: (number #, card number #, borrowing date, also book dates)
Note: Number, card number is a candidate keyword and there is a foreign key constraint; the date (RETURN_DATE) either NULL, or greater than the date of borrowing
2, inquiry
l Ask for the number of books, the number, the highest price, the lowest price.
l List the book in ten books (book name, author, publishing house, year).
l Which publishers have more than 100 total books.
l How many books have been borrowed?
L years for the longest book.
l "Database system principle tutorial, Wang Shan, Tsinghua University Press, published in 1998"?
l Which year published the number of bookbooks? (Tip: Group added)
l The average number of booklets per bidding certificate. (Tip: It is necessary to consider the book license for non-borrowing books) L this year (2003) did not borrow books.
l Which department is the average borrowing book? (Tip: Connection plus group plus query)
l The books that have not been booked in the last two years (2002, 2003).
l This year (2003) The book borrowed most?
Experimental report: (content and requirements)
1. SQL script (.doc)
2. Review the contents of the experiment three, insert some test data in the table with SQL
3. Submit SQL (.doc)
4. Submit an experimental report in writing in writing and submitted in strict accordance with the format of Annex 1 and Annex 2.
Experiment five SQL functions and expressions
Purpose:
1. Master the main functions in Transact-SQL
2, learn the use of SQL expressions
Experimental requirements:
1. Skilled the collection function, data conversion function, and date functions
2, learn to use SQL expressions
3, understand the identifier, the use of wildcard
Experimental environment and time schedule:
1, SQL Server7.0
2, learn: 3 hours
Experimental content:
1, set function
l Query the average advance payment of all kinds of books and sales so far.
(Tip: Using Group by
l According to the condition TYPE = 'psychology' to generate the Titles table in the Titles table Price and Advance
(Tip: Using Compute)
l Statistics 1992, 1993, the number of sold books (Table Sales) in 1994
(Tip: Using a CASE clause)
Compare the difference between Group By, Compute, and CASE
2, data type conversion function
l From the Titles table The query condition is type = 'trage_cook', the first two of the first two of the YTD_SAles 15 Title and YTD_SALES field content
(Tip: Because YTD_SAles is int type, you must convert it into a character type first by matching comparison.
l Convert the current date to a pattern 101
3, date function
l Determine the data library (title table) title publishing date and number of days during the current day
l Sales volume sales in the first 10 days of sales table (Sales)
l Get the year, month, day, hour, monthly, month, hour, respectively.
4, mathematical function
There are a lot of mathematics functions in SQL, similar to the usual mathematics functions, here we only introduce two functions
Rand (), ROUND (), with a view to throw bricks. I hope everyone can help it in practice.
l Generate a random number random_number with 2 for random seeds
l Take the number of numbers 1223.75 integers and striking and finishing
5, string function
l Seeking from 17 characters from 17 characters in string 'the call me the hunter', 'h' appears
l Returns the last name in the Authors table, returns the first letter in the Authors table in another column.
l Replace the string CDE in Abcdefghi with ZZZ. l Combine string 'hello' and 'Join', join a space in the middle
6, expression
l Display the author name in the author of the Author table
l Query a group of specific publishers (which is the publisher of California (CA); the publication of the average price of more than 10.00 US dollars)
(Tip: First let's see the source of the data field, the publisher name is from the publishers table; then see the conditions, the list of publishers comes from the Publishers table, and the average price it publishes is taken from the Titles table, so I will join first. Titles and Publishers Table to create a query, add query conditions and group information)
Thinking: WHERE and HAVING
The Having clause is similar to the WHERE clause, but is only applied as a group (i.e., the WHERE clause applies to the individual rows), while the WHERE clause is applied to individual rows.
l The head identifier of the query, the annual sales of these books are from 4,095 to 12,000.
(Tip: Requirements for conditions to be implemented with Between and> l Query all books without marking prices Thinking: Differences between NULL values and null values l Query all books and prices of books, if there is no price, set the price to 0 7, identifier and wildcard Learning%, _, [], [^], learning escape [] usage l Query the phone number with all the author's name and phone number at 415 l Query all the authors of the six letters ending with 'HERY1' l Find the author's name of the author of 'M' and the second letter is not 'c' l Find the author name of '_n' Experimental report: (content and requirements) 1. SQL (.doc) submitted to the relevant practice 2. Summary the functions mentioned in the experiment (provided in list) 3. Submit an experimental report in writing in writing and submitted in strict accordance with the format of Annex 1 and Annex 2. Experimental hexafa and stored procedure Purpose: 1. Learn the creation of SQL Server triggers 2, learn the creation and call of the stored procedure Experimental requirements: 1. Skilled the trigger and the creation of the stored procedure 2, experience the role of trigger Experimental environment and time schedule: 1, SQL Server7.0 2, learn: 2 hours Experimental content: This experiment is based on experiments, two, and has established a good trigger and stored procedure. 1, trigger l Create a trigger of the teacher table, enabling the teacher table into the Teachernum field in the department's table when the leadership record is updated to identify the department. l Disable the trigger of the teacher table Leader_Insert l Trigger that enables teachers table 2, stored procedure l Create a stored procedure delStudent, delete student numbers equal to student records of incoming parameters, and delete the student's selection record l Re-query analyzer uses stored procedure DELSTUDENT to delete student records for '111001' l In the example database, if there is a stored procedure 'titles_sum', delete it. l Create a stored procedure Salequa in the PUBS in the Example Database, query a stock ID is all sales records for @stor_id, and return a sales quantity parameter @SUML to execute the stored procedure Salequa created in the previous step, enter the parameter @STOR_ID = '7131' And generate sales information according to the output parameters, the format is as follows: Sales level is [rating], sales volume is [Sales] If the sales volume is <= 50, the [rating] is 3; if the sales volume> 50, if the sales volume is <100, the [rating] is 2: If sales > 100, [Level] is 1. Experimental report: (content and requirements) 1. Create a trigger, the SQL script (.doc) of the stored procedure (.doc) 2. Submit an experimental report in writing in writing and submitted in strict accordance with the format of Annex 1 and Annex 2. Experiment seven database advanced skills (cursor, transaction and error handling) Purpose: 1. Master the application of cursor 2. Definition and error handling of learning affairs Experimental requirements: 1. Proficiency in the definition of cursor, and experience its use in practice 2, understand the role of affairs, and define a transaction, use transactions in SQL programming 3. Master basic error Experimental environment and time schedule: 1, SQL Server7.0 2, learn: 4 hours Experimental content: a) cursor i. Define a read-only cursor on the paradigm database PUBS, query the AU_ID, AU_LNAME, AU_FNAME field of the author (Authors) table. Ii. Putting the previous only cursor to define a standard and update cursor, respectively. III. Using the update cursor CUR_AUTHORS_UPDATE to change the AU_ID equal to '172-32-1176' recorded to 'Smith' and 'Jake', respectively. IV. Paging Storage Procedure In practice, we often encounter such problems: We read data from a table on the interface, because the table has a lot of data, one-time read is obviously too slow and occupied a large number of network bandwidth, should We read the data with a stored procedure paging, here we give an example (use the example database PUBS): Write a storage procedure ProtitlesPages Sequence in the Pubdate field, from the Titles table to read the data, 10 records per page (Tip: It is clear that protitlespages need a few parameters of the current page, how many records per page, how many pages are b) transaction and error handling i. Run the following SQL statement, write operation results, experience the role of transactions SELECT TOP 5 TITLE_ID, STOR_ID from Sales Order by title_id, stor_id Begin TRAN Delete Sales SELECT TOP 5 TITLE_ID, STOR_ID from Sales Order by title_id, stor_id Go Rollback TRAN SELECT TOP 5 TITLE_ID, STOR_ID from Sales Order by title_id, stor_id II. Delstudent, Salequa in Experimental Six, including two SQL statements, add transaction processing and error processing Experimental report: (content and requirements) 1. Establish a cursor with the SQL script (.doc) 2 of the transaction. Query results in transaction processing 3. Submit an experimental report in writing in writing and submitted in strict accordance with the format of Annex 1 and Annex 2.
