关键词 > COMP2350/6350
COMP2350/6350 Database Systems – Week 2
发布时间:2022-08-25
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
COMP2350/6350 Database Systems
Tutorial Sample Solution – Week 2
1. (*) Why would you choose a database system instead of using traditional file-based system? When would it make sense not to use a database system?
A database is an integrated collection of data, usually so large that it has to be stored on a secondary device such as disk or tapes. The data in the database is managed by software known as database management system (DBMS). Some of the major advantages of using database approach
are:
• Data independence and efficient access
• Reduced application development time
• Data integrity and security
• Concurrent access and crash recovery
If the advantages mentioned above are not important for the application at hand, using the file-based system is the option of choice because of the increased cost and overhead of purchasing and maintaining a DBMS.
2. Listfour examples of database systems thatyou use in your everyday life.
Some example could be:
• University database that keep details of students, staffs, courses etc.
• Library database that maintains details of books, research articles, electronic resources etc.
• Bank database keeps data about accounts, staff, customer, loan etc.
• Airlines database that keep details of Flights, Planes, airports etc
3. Listfive major database management systems (DBMS) those are widely used today.
Some examples could be:
• Microsoft SQL server
• PostgreSQL
• Microsoft Access (Maybe mention MongoDB/NoSQL)
4. (*) What are the responsibilities of a DBA? If we assume that the DBA is never interested in running his or her own queries, does s/he still need to understand query optimization? Why?
The DBA is responsible for:
• Designing the logical and physical schemas, as well as widely-used portions of the external schema
• Security and authorization
• Data validity and recovery from failures
• Database tuning (the DBA is responsible for evolving the database, in particular the conceptual and physical schemas, to ensure adequate performance as user requirements change)
A DBA needs to understand query optimization even if s/he is not interested in running his or her own queries because some of the above responsibilities (database design and tuning) are related to query optimization. Unless the DBA understands the performance need of widely used queries, and how the DBMS will optimize and execute these queries, good design and tuning decisions cannot be made.
5. (*) Discuss the concept of data Independence and explain its importance in a database environment.
See Section 2.1.5.
6. (*) Explain the difference between external, internal and conceptual schemas. How are these different schema layers related to the concepts of logical andphysical data independence?
• External schema corresponds to different views of the data.
• Conceptual schema describes all the entities, attributes, and relationships together with integrity constraints.
• Internal schema contains the definition of stored records, the method of representation, the data fields, and the indexes and storage structure used.
Use Figure1 to explain the second part of the question. For further details, see section 2.1.5 of the textbook.
Figure1: Data independence and the three-level architecture
7. (*) Which of thefollowing plays an important role in representing information about the real world in a database? Explain briefly.
a) The data definition language (DDL)
b) The data manipulation language(DML)
The data model
d) The buffer manager
• The data definition language is important in representing information because it is used to describe external and logical schemas.
• The data manipulation language is used to access and update data; it is not important for representing data.
• A data model is fundamental to representing data. The data model (or datamodel) is an abstract model that organizes elements of data and standardizes how they relate to one another and to properties of the real world entities. The DDL is just the specific set of language available to describe an actual application’s data in terms of the data model.
• The buffer manager is not very important for representing data because its job is to bring arbitrary disk pages into main memory, independent of any data representation.
8. (*) Consider a two-dimensional integer array of size m×n that is to be used in your favorite programming language. Using this array as an example, illustrate the difference (a) between the three levels of data abstraction, and (b) between a schema and an instance.
.
• The physical level would simply be m×n (probably consecutive) storage locations of size specified by the implementation (eg. 32 bits each).
• The conceptual level is a grid of boxes, each possibly containing an integer, which is n blocks high and m boxes wide
• There are 2m×n possible views. eg. a view might be the entire array, or a particular row of the array, or all n rows but only columns 1 through 3.
.
Let’s use Pascal.
type tgrid = array[1..n, 1..m] of integer
var vgrid1, vgrid2 : tgrid
Then tgrid is a schema, whereas vgrid1 and vgrid2 are instances. Consider the schema array [1..3, 1..2] of integer. An instance of this schema is:
1 |
16 |
3 |
5 |
6 |
8 |