If you need real questions, please leave your email address.
20 10 March Computer Rank Examination Level 4 Database Written Test Questions (Text Version)
First, multiple choice questions
(1) In the requirement analysis stage of database application system, data security needs to be considered. The following do not belong to the data security requirements analysis content is
A) Analyze the security requirements of data to ensure that the data constraints defined on each relational table can meet the use requirements.
B) Analyze the data access requirements of global users and determine the security control strategy of global data.
C) Analyze the data access requirements of special users to ensure that the security control strategy of the database can meet their use requirements.
D) Analyze the data access requirements of different users to determine the data that different users can operate.
(2) There is a student form (student number, name, gender, ID number, date of birth, department number), and the following statements are used to build an index on this form:
Create nonclustered index idx _ nameon on student table (name).
This index is called
A) nonclustered hash index
B) nonclustered secondary index
C) nonclustered sequential index
D) nonclustered primary index
(3) In the stage of database physical design, organizing data by using aggregate files can improve the efficiency of some queries. The following descriptions about aggregation files and aggregation keys are incorrect.
A) Aggregation files store related records in different relational tables together to reduce the physical I/O times of the system.
B) Aggregation keys should select attributes or attribute groups that are often used for natural connections.
C) Aggregating files can improve the speed of some connection queries, but may slow down the processing speed of other types of queries.
D) The higher the data repetition rate of an attribute in a relational table, the more suitable it is as an aggregation key.
(4) It is known that there is a model describing the relationship between scientific research projects and class teachers: scientific research projects (project number, project name, funding, class teacher number, name, title), which satisfies the functional dependency set: F={ project number → project name, project number → funding, project number → class teacher number → name, class teacher number → title}. The most reasonable relational model that conforms to 3NF in the following decomposition is
A) scientific research projects (project number, project name and funds) and teachers (teacher number, name and title)
B) Scientific research projects (project number, project name and funds) and teachers (teacher number, name, professional title and project number)
C) Scientific research projects (project number, project name, funds, number of teachers in charge) and teachers (teacher number, name and title)
D) scientific research project (project number, project name and funding), project _ teacher (project number, responsible teacher number) and teacher (teacher number, name and title)
(5) In the IDEF0 diagram, rectangular boxes represent functional activities. The following statement is wrong about the semantics of arrows connected at four sides of a rectangular box.
A) The arrow on the left indicates the data needed to complete the functional activities, emphasizing the content consumed or transformed by the activities.
B) The arrow above indicates the event or constraint that affects the execution of the activity, and it emphasizes the content transformed by the activity.
C) The arrow on the right represents the information generated by the activity, emphasizing the results of the transformation and processing of the activity.
D) The arrow below indicates the means to implement the activity or the resources needed to complete the activity, and emphasizes how to do it.
(6) The database administrator is mainly responsible for the operation, management and maintenance of the database, including daily maintenance, system monitoring and analysis, performance optimization, etc. The following statement about the job content of a database administrator is wrong.
A) Database backup and recovery is an important maintenance work. Database administrators should make different backup plans according to different application requirements, which should include backup time, cycle, backup method, backup content and so on.
B) Performance optimization is an important task for database administrators. The main methods of performance optimization include query optimization, index adjustment and mode adjustment. These tasks usually do not require the participation of developers.
C) The database administrator should monitor the use of various locks in the database and handle possible deadlocks. If problems are found, the database administrator should inform relevant personnel in time.
D) Database administrators need to regularly check the usage of storage space and expand the storage space according to the demand, generally without the participation of end users.
(7) If there are four waiting transactions T0, T 1, T2 and T3 in the system, where T0 is waiting for data item a 1 locked by T 1, T 1 is waiting for data item A2 locked by T2, T2 is waiting for data item A3 locked by T3, and T3 is waiting for data item A0 locked by T0. According to the above description, the state of the system is
A) live lock
B) deadlock
block
D) normal
(8) In SQL Server 2000, there is a teacher table (teacher number, name and title) in a database, in which the data type of the teacher number is integer and the others are character types. If there is no data in the teacher table at present, the user will execute the following statement several times:
I. Start trading T 1
Two. Insert teacher table value (1000,' Zhang San',' teaching assistant');
Three. Insert teacher table value (100 1,' Wang Er',' teaching assistant');
Iv. submit t1;
ⅴ. Start trading T2
ⅵ. Insert teacher table values (1002,' Wang San',' Lecturer');
ⅶ. Insert teacher table values (1003,' Li Si',' Lecturer');
ⅷ. Submit T2;
When executing VII, the server where the database is located suddenly loses power. When the database system is restarted, the number of data contained in the teacher table is
A) article 4
B) article 3
C) article 2
D) article 0
(9) It is known that the student relationship (student number, name, gender, course number, grade and department number) has the following functional dependence.
ⅰ. (student number, course number) → course number ⅱ. (student number, course number) → grade.
Ⅲ. (student number, course number) → department number Ⅳ. (student number, course number) → name, gender.
ⅵ. (learning bow, table number) → learning number
The above functional dependencies belong to nontrivial functional dependencies.
A) I and v only
B) only Ⅱ and V.
C) ⅲ and ⅳ only
D) Category II, III and IV only
(10) The database management system adopts a three-level lock protocol to prevent data errors that may be caused by concurrent operations. In the three-level locking protocol, 1 level locking protocol can solve the following problems.
A) missing modifications
B) you can't read it repeatedly.
C) reading dirty data
D) deadlock
(1 1) In SQL Server 2000, if a database is used for a period of time, the space of its data file and log file is almost used up. If you want to manually expand the space of the database, the following statement is correct.
A) You can increase the size of a single data file, but you cannot increase the size of a single log file.
B) Data files and log files of the whole database can be enlarged at one time.
C) You can add new disks and create new data files and log files for the database on the new disks.
D) You can scale up the size of the entire data file at one time, but you cannot scale up the size of the log file.
(12) In SQL Server 2000, there is an employee table (employee number, employee name and email). Now you need to limit the last few digits of the email to "@abc.com", with at least one character before "@". The following statements that add constraints are correct.
A) change the employee list
Add constraint chk_email check (email like' _%@abc.com').
B) change the employee list
Add constraint chk_email check (e-mail = '_%@abc.com')
C) change the employee list
Add constraint chk_email check (email such as' _@abc.com')
D) change the employee list
Add constraint chk_email check (e-mail = '_@abc.com')
(13)OLE DB is a general data access interface. The following statement about OLE DB is wrong.
A)OLE DB is the implementation of Microsoft OLE object standard, which consists of a series of COM interfaces.
B) Unlike ODBC drivers that need to support almost all DBMS functions, OLE DB only allows some DBMS functions to be supported.
C)OLE DB supports access to relational and non-relational databases.
D)OLE DB defines three types of data access components: data provider, data consumer and data-driven sorting.
(14) log file is one of the important mechanisms to ensure the correctness and consistency of data after the database system fails. The following statement about log files is wrong.
A) The registration order of logs must be strictly in accordance with the time sequence of transaction execution.
B) In order to ensure that data can be recovered correctly in case of failure, it is necessary to write the database first and then the log.
C) Checkpoint record is a record of log file, which is used to improve recovery efficiency.
D) Log files must be used for transaction fault recovery and system fault recovery.
(15) Unified Modeling Language (UML) is a well-defined, easy-to-express, powerful and universal visual modeling language. The following statement about UML is wrong.
A) In the use case model of UML, one role can execute multiple use cases, and one use case can be used by multiple roles.
B)UML is also suitable for object modeling, in which class diagrams and object diagrams are used to describe class models, object models and their dynamic relationships. These two diagrams belong to the dynamic view of UML.
C) c) The package diagram of UML is a class diagram showing the relationship between packages, and packages are the result of grouping the elements involved in the model. When grouping elements, we should try our best to pursue the goals of low coupling between packages and high cohesion within packages.
D) Sequence diagram and collaboration diagram in D)UML both describe interaction, but sequence diagram emphasizes time and collaboration diagram emphasizes space.
(16) Dumping data in the database in time is an important means to ensure data safety and reliability. The following statements about static dump and dynamic dump are correct.
A) During static dump, the database system cannot run other transactions, and data cannot be inserted, modified or deleted during dump.
B) Static dump must rely on database logs to ensure the consistency and validity of data.
C) Dynamic dump can only be started after the running transaction is completed.
D) For the business system with 24-hour business, static dump technology is more suitable.
(17) suppose there is a table T(T(a 1, a2, ... In a parallel database system. Because of the large amount of data, it is necessary to divide the data into different disks by one-dimensional partition method to improve the system efficiency. Let al be the partition attribute, and most of the query operations on table T are point queries, so the appropriate tuple partition strategy is as follows
A) rotation method and scope division
B) rotation method and hash segmentation
C) hash division and range division
D) None of the above is appropriate.
(18) The software development model is a normative description of the software development process, which is used to express the overall framework of the software development process and the relationship between the various stages of software development activities. The following models belong to software development models.
Ⅰ. Blasting model Ⅱ. rapid prototype model
III. Incremental mode IV. Spiral model
A) only Ⅰ and Ⅱ
B) only Ⅰ, Ⅲ and Ⅳ
C) only Ⅱ and Ⅲ
D) one, two, three and four
(19) owns large chain stores, business-oriented database application system and decision-oriented data warehouse system. The data warehouse system needs to import new data from the database of the database application system every night. In the following data table, the most inappropriate way to obtain data from database application system through snapshot is
A) commodity list and commodity category list
B) list of employees and suppliers
C) commodity sales table and commodity purchase table
D) organization chart of shopping center
(20) In database application system, query efficiency is an important aspect of evaluating database application system. In the following methods, it is usually beneficial to improve the query efficiency.
I. Avoid useless sorting operations as much as possible.
Ⅱ. Eliminate sequential access to large table row data as much as possible.
Iii. Avoid using negative queries as much as possible.
Ⅳ. Avoid using external connection operation as much as possible.
ⅴ. Use subqueries instead of join queries as much as possible.
A) Only one, two, three and four
B) only I, II and IV
C) Level 2, 3, 4 and 5 only.
all
The conceptual design of (2 1) database needs to analyze and organize the data involved in the application of an enterprise or organization. The existing design contents are as follows
I. analyze the data and determine the entity set.
Ⅱ. Analyze the data to determine the relationship between entity sets.
Ⅲ. Analyze the data and determine the storage mode of each entity set.
Ⅳ. Analyze the data and determine the cardinality of the relationship between entity sets.
ⅴ. Analyze the data and determine the data volume of each entity set.
ⅵ. Analyze the data to determine the attributes contained in each entity set.
The above contents do not belong to the conceptual design of database.
A) Only one, four and six.
B) only Ⅱ and ⅴ
C) only ⅲ, ⅳ and ⅵ
D) Class III and Class V only.
(22) Client, server (C/S) and browser/server (B/S) are two commonly used database application systems. About C/S and B/S architecture, the following statement is wrong.
A) In the C/S and B/S architectures, the database server undertakes the functions of data sharing and management.
B) If the end-user group of the system is large and the users are widely distributed, B/S architecture should generally be adopted.
C)C/S architecture applications need to be distributed to each user and installed on each user's computer. B/S architecture generally uses a general browser as the business application interface, and more complex data processing functions can generally be realized by components interacting with the browser.
D) Generally speaking, for database application system projects with the same functional requirements, the application development cost of C/S architecture is higher than that of B/S architecture.
(23) There are various types of data in the database application system environment, including the following data.
I. Indexes established to improve query efficiency
Ⅱ. Data dictionary describing table structure information
Ⅲ. Statistical analysis data for optimizing query
Ⅳ. User session information
ⅴ. User query results
The above data will generally be stored in the database.
A) only Ⅰ, Ⅱ and ⅴ
B) only Ⅰ and Ⅲ
C) only Ⅰ, Ⅱ and Ⅲ
D) Category II, III and IV only
(24) In the life cycle of database application system, system planning and analysis is an important link. Regarding system planning and analysis, the following statement is wrong.
A) Planning and analysis are the starting points of the life cycle of database application systems. The main work in the planning and analysis stage includes system planning and definition, feasibility analysis and project planning.
B) System planning and definition are the main links of planning and analysis, which need to be completed gradually in the process of system design.
C) Feasibility analysis is to analyze the feasibility of the project from the aspects of technology, economy and operation. After defining the objectives and tasks, it evaluates whether the system is feasible under given conditions.
D) Project planning is to reasonably estimate the resources, expenses and progress required by the project and make a preliminary project development plan.
(25) In SQL Server 2000, the amount of data in the database is relatively large, and it is stored on a storage device without a disk array. In order to improve the efficiency of data operation as much as possible, among the following methods of establishing database files, the most appropriate method is
A) Select a disk as large as possible and create data files and log files on it.
B) Establish as many data files as possible, so that each data file is located on a different disk, and establish the log file I and the data file on different disks as far as possible.
C) Create as many data files as possible, build these data files on a disk large enough, and build all log files on another disk large enough.
D) Establish as many data files as possible, so that each data file is located on a different disk, and establish the log file and data file on the same disk as far as possible.
(26) The database application system of large supermarket chains needs to be tested in the process of implementation or transformation. The following test activities are available
First, simulate customer consumption behavior, generate sales data, and test whether the system can correctly complete the sales business.
Ⅱ. Write a simulation data generator to generate sales data corresponding to 2000 sales businesses per second, and test the system throughput.
Ⅲ. Simulate and generate 500 million pieces of sales data in the database, and open all checkout counters at the same time to test the average response time of the system.
Ⅳ. The simulated sales lasts for one week, during which the system checkout function is checked every day.
ⅴ. Test whether the database application system can correctly complete the UnionPay card consumption business.
The above testing activities belong to performance testing.
A) only Ⅰ, Ⅱ and Ⅲ
B) only Ⅱ, Ⅲ and ⅴ
C) only Ⅱ and Ⅲ
D) Only I, IV and V.
(27) In SQL Server 2000, there is a curriculum (course number, course name, credits and starting semester). Now, an embedded table-valued function named f _FindTotal is established on this relational table to query the total number of courses offered in the specified semester. The correct code to implement this function is
A) create the function f _ find total(@ semester integer)
Return form
be like
Return (
Select COUNT (course number) as the number of courses, and SUM (credits) as the total credits of courses.
Where does the semester start = @ semester)
B) create the function f _ find total(@ semester integer)
Return form
be like
Return (
Select Count (course number) as the number of courses, and Count (credits) as the total credits of courses.
Where does the semester start = @ semester)
C) create the function f _ find total(@ semester integer).
Return form
be like
Return (
Select SUM (course number) as the number of courses, and SUM (credits) as the total credits of the course.
Where does the semester start = @ semester)
D) create the function f _ find total(@ semester integer)
Return form
be like
Return (
Select SUM (course number) as the number of courses and COUNT (credits) as the total credits of the course.
Where does the semester start = @ semester)
(28) Establish a shopping mall business system, including a sales document table (sales document number, cashier and sales time), a sales list (sales detail item number, sales document number, commodity number, quantity, unit and amount) and a commodity table (commodity number, trade name and price). The system is busy and has a large amount of data. The cashier's total income should be counted at each shift change, and the sales volume of each commodity should be counted after the end of business every day. In the process of sales, it is often necessary to query a sales business. In order to optimize the system performance, the following optimization scheme is reasonable.
A) Establish a clustered index for cashier attribute of sales document table, a nonclustered index for sales document number attribute of sales plan, and a clustered index for commodity number attribute of commodity table.
B) Establish a clustering index on the sales document number attribute of the sales document table, a non-clustering index on the sales document number attribute of the sales schedule, a clustering index on the commodity number attribute of the commodity table, a clustering index in the cashier single-shift sales table and the commodity sales summary table, and accumulate corresponding data after each sales transaction.
C) Set a clustering index for the sales time attribute and cashier attribute of the sales document table, a non-clustering index for the sales document number attribute of the sales list, a clustering index for the commodity number attribute of the commodity list, a cashier single-shift sales table and a commodity sales summary table, and accumulate corresponding data after each sales transaction.
D) Establishing a clustering index for the sales document number attribute and cashier attribute of the sales document table, and establishing a clustering index for the commodity number attribute of the sales schedule.
(29) In SQL Server 2000, it is assumed that a database with a large amount of data and logs is completely backed up by a backup device. The following statement is correct.
A) If the remaining space on each disk is not enough to store the backup of the database, but the sum of the remaining space of all disks is greater than the space required for backing up the database, you can organize the remaining space of these disks to establish backup devices to realize the backup.
B) If the remaining space on each disk is not enough to store the backup of the database, but the sum of the remaining space of all disks is greater than the space required for backing up the database, data and logs in the database can be backed up to backup devices located on different disks to solve the problem of insufficient space on a single disk.
C) Only the data in the database can be backed up, which can be backed up to backup devices located on different disks, so as to minimize the space occupied by the backup database.
D) If the remaining space on each disk is not enough to store the backup of the database, but the sum of the remaining space of all disks is greater than the space required for backing up the database, you can set up a backup device on each disk and use these devices to back up the database at the same time.
(30) In the distributed database system, let the schema of a data table be S(a 1, a2, a3, a4, a5), in which A1is the main code, and the data volume of this table is large, and the value space of a2 is {1, 2,3}. There are two data slicing schemes, namely PSZ 1 = {S 1 1, S 12, S 13} and PS2 = {S22, S23}, and any slice is s1i. The mode of S2 1 is (a 1, a2), and S22 is (a65438). Then the following statement is correct about the fragmentation mechanism of PS 1 and PS2.
A)PS 1 is a horizontal slice, and PS2 is a vertical slice.
B)PS 1 is the vertical slice, and PS2 is the lead-out slice.
C)PS 1 is the horizontal slice, and PS2 is the lead-out slice.
D)PS 1 is a vertical slice, and PS2 is a horizontal slice.
Second, the application problem
(1) In SQL Server 2000, it is necessary to establish a unique nonclustered composite index on the first_name and last_name columns of the employees table, in which the repetition rate of the first_name column data is 5% and the last_name column data is 10%. Please complete the following statements to make the query based on the First Name and Last Name columns most effective.
Create a unique nonclustered index Idx_Name
About employees (1)
(2) In SQL Server 2000, there is a customer table (customer number, customer name, region and age), and the application system needs to count the number and average age of customers in the designated region. Complete the following stored procedure code to complete this function.
Create process P_Count
@area varchar(20)
be like
Select COUNT(*) as the number of people and 2 as the average age from the customer table.
Where is it located =@area
(3) There is a customer list (customer number, customer name and contact number) and a purchase list (product number, customer number and purchase time). Now we need to count the number of customers with purchase records. Please complete the following statement to realize this function.
Select 3 from the purchase table.
(4) In VB 6.0, in order to simplify the programming of accessing database data, Adodc data control is often used to access the data in the database. The property used to describe the server connection information in this control is 4.
(5) In SQL Server 2000, it is reasonable to grant users in the database the right to insert, delete and modify all user data tables in the database by adding users to the five database roles provided by the system.
(6) There is a student table (student number, name, gender, department), and these columns are not allowed to have empty values. Now it is necessary to establish a viewpoint to count the number of boys and girls in the computer department. Please complete the following statement.
Create view V_Count
be like
Select 6, COUNT(*) as the number of students in the student table.
Where Department =' Computer Department'
seven
(7) A transaction consists of a series of operations, and the execution of the transaction is represented by the execution of each operation in the transaction. Every transaction should have an end operation. When a transaction needs to terminate and cancel all data modifications that have been performed, the statement that should be executed is 8.
(8) The data warehouse is a 9-oriented, integrated, nonvolatile and time-varying data set.
(9) The type of transaction operation on the data item determines the type of lock on the data item. There are two kinds of locks: mutual exclusion lock (X lock for short) and 10 lock (S lock for short).
(10) In SQL Server 2000, the database DB 1 will be differentially backed up to the backup device BK, and the existing contents on BK will not be overwritten. Please complete the following statement about backing up the database DBI.
Back up 1 1 DB 1 to BK 12, NOINIT.
(1 1) In SQL Server 2000, a database contains a book list (book number, title and category) and a sales list (book number, sales time and sales quantity), in which the sales quantity is greater than 0. Now I want to inquire about the names and categories of books sold in June 5438+October 2009/KLOC-0. Please complete the following query statement to realize this function.
Select the book name and category from the book list.
Existence location (select * from sales table)
The sales time is between' 2009/ 10/ 1' and' 2009/ 10/3 1'
13 )
(12)OLAP is mainly used to support complex analysis operations, focusing on data support for decision makers and senior managers. There are three main ways to realize OLAP: MOLAP, 14 and HOLAP.
(13) In the distributed database system, the transparency of data distribution is an important guarantee for users to use data conveniently. The highest level of data distribution transparency is 15 transparency.
Third, design and application issues.
(1) has a simplified online shopping system, and its ER diagram is as follows:
The following constraints exist between system data:
A customer (with a unique number) can have multiple orders, and each order belongs to only one customer;
Ⅱ. An order (with a unique number) can contain multiple order details, and each order detail belongs to only one order;
Ⅲ. A commodity can appear in multiple order details, and an order detail only contains one commodity;
Ⅳ. A commodity category can contain multiple commodities, and a commodity can only belong to one commodity category.
① Please mark the cardinality of the relationship between entity sets in the ER diagram according to the above constraints. (2 points)
(2) Please convert the ER diagram into a relational schema conforming to 3NF, and give the main code and outer code of each relational schema. (4 points)
③ Please modify the designed relational schema to support the following functions (only the modified relational schema is listed). (4 points)
First, it is convenient for customers to know the processing status and arrival time of each order detail;
Two. It is convenient for customers to inquire about the serial number, name, city, contact number, reputation and other information of commodity suppliers. (Only one supplier can supply multiple commodities, and one commodity can only be selected from one supplier)
(2) The student status management system of a school realizes the management of student status information, in which the student table structure is: student table (student number, name, gender, department number, whether or not you have a student status), and all columns in this table are not indexed except the student number.
(1) Establishing a student status management system often performs the following operations:
SELECT*FROM student table
In which, name =' Zhang San', department number =' 10', and whether you have a student status =' Yes'.
It is found that this operation is inefficient in the process of system operation. The system engineer suggested that since the three columns of "student number", "name" and "student status" were used in the query conditions, it was suggested to establish indexes on these three columns respectively to improve the query efficiency.
Can all three indexes suggested by it improve the query efficiency? Please briefly explain the reasons.
(2) There are the following data in the student table:
And a nonclustered index (sorted by pinyin in ascending order) is established on the name column of the student table. Please give a schematic diagram of the secret index corresponding to the search code "name". (4 points)
(3) In SQL Server 2000, there is a teacher table (teacher number, surname, department number and title) and a department table (department number, department name and number of senior titles). Please write a post-trigger trigger that meets the following requirements (let the trigger be named tri_zc). (10)
Whenever a teacher with a senior title ("professor" or "associate professor") is inserted into the teacher table, or the title of a teacher with a non-senior title is changed to a senior title, the number of senior titles in the corresponding department in the department table will be modified. (Assuming that only one teacher's title is inserted or changed at a time)