存在的问题

1. incorrect data - 文件无法限制具体年月日类型，也无法控制会员数量，可能写错为a
2. BandName 用户可以随便输入，无法限制指定的BandName。所以可能输入了不存在的
3. wrong order - 文件系统无法对会员数进行排序
4. The data privacy issue with a single file - 安全问题，任何人都可以查看txt文件
5. data redundancy - 数据冗余

limitation of a file-based approach

• There is no validation technique that could detect the original error.
• There is also possibly an error that is not evident from looking at the file contents.
• A file-based approach can lead to data integrity problems in an individual file.

The data privacy issue with a single file

A different problem is a lack of data privacy.

The problem is that there cannot be any control of access to part of a file.

Data redundancy and possible inconsistency in multiple files

There is now data duplication across the files. This is commonly referred to as data redundancy.

Data dependency concerns

When file structures have been defined to suit specific programs, they may not be suited to supporting new applications.

In the relational database model, each item of data is stored in a relation which is a special type of table. A relational database is a collection of relational tables.

In a database design, a table would be given a name with the attribute names listed in brackets after the table name.

Member(MemberID, MemberGivenName, MemberFamilyName, BandName, ...) Band(BandName, AgentID, ...)

A row in a relation should be referred to as a tuple but this formal name is not always used. Often a row is called a ‘record’ and the attribute values ‘fields’. The tuple is the collection of data stored for one ‘instance’ of the relation.

1. The most important feature of the relational database concept is the primary key. A primary key may be a single attribute or a combination of attributes. Every table must have a primary key and each tuple in the table must have a value for the primary key and that value must be unique.
2. Once a table and its attributes have been defined, the next task is to choose the primary key. In some cases there may be more than one attribute for which unique values are guaranteed. In this case, each one is a candidate key and one will be selected as the primary key.
3. A candidate key that is not selected as the primary key is then referred to as a secondary key. Often there is no candidate key and so a primary key has to be created.
4. A database can contain stand-alone tables, but it is more usual for each table to have some relationship to another table. This relationship is implemented by using a foreign key.
5. This provides referential integrity which is another reason why the relational database model helps to ensure data integrity.

• We can use a top-down method called stepwise refinement to break down the process of database design into simple steps
• In database design this approach uses an entity-relationship (E–R) diagram.
• An entity (strictly speaking an entity type) could be a thing, a type of person, an event, a transaction or an organisation.
• there must be a number of ‘instances’ of the entity.

Requirement

The agency needs a database to handle bookings for bands. Each band has a number of members. Each booking is for a venue. Each booking might be for one or more bands.

Step 1: Choose the entities

You look for the nouns. You ignore ‘agency’ because there is only the one. You choose Booking, Band, Member and Venue. For each of these there will be more than one instance. You are aware that each booking is for a gig at a venue but you ignore this because you think that the Booking entity will be sufficient to hold the required data about a gig.

This requires experience, but the aim is not to define too many. You choose the following three::

• Booking with Venue
• Booking with Band
• Band with Member.

字段为 ' 家庭信息 ' 和 '学校信息 '违反了原子性，

Normalisation is a design technique for constructing a set of table designs from a list of data items. It can also be used to improve on existing table designs.

First normal form (1NF)

The conversion to first normal form (1NF) requires splitting the data into two groups

Second normal form (2NF)

Examine each non-key attribute and ask if it is dependent on both parts of the compound key. Any attributes that are dependent on only one of the attributes in the compound key must be moved out into a new table.

Third normal form (3NF)

Examined to see if there are any non-key dependencies; that means we must look for any non-key attribute that is dependent on another non-key attribute. If there is, a new table must be defined.

• The DBMS provides soft ware tools through a developer interface. These allow for tables to be created and attributes to be defined together with their data types.
• The DBMS provides facilities for a programmer to develop a user interface.
• It also provides a query processor that allows a query to be created and processed. The query is the mechanism for extracting and manipulating data from the database.
• The other feature likely to be provided by the DBMS is the capability for creating a report to present formatted output. A programmer can incorporate access to queries and reports in the user interface.

An important feature of the DBMS is the data dictionary which is part of the database that is hidden from view from everyone except the DBA. It contains metadata about the data. This includes details of all the definitions of tables, attributes and so on but also of how the physical storage is organised.

There are a number of features that can improve performance. Of special note is the capability to create an index for a table.

CREATE DATABASE BandBooking; CREATE TABLE Band (
    BandName varchar(25),
    NumberOfMembers integer); 
ALTER TABLE Band ADD PRIMARY KEY (BandName); 
ALTER TABLE Band-Booking ADD FOREIGN KEY (BandName REFERENCES Band(BandName);

• The SQL consists of a sequence of commands.
• Each command is terminated by;
• A command can occupy more than one line.
• There is no case sensitivity.
• There has been a decision made here to use upper case for the commands and lower case for table names, attribute names and datatypes.
• When a command contains a list of items these are separated by a comma.
• For the CREATE TABLE command this list is enclosed in parentheses

There are three categories of use for Data Manipulation Language (DML)

• The insertion of data into the tables when the database is created
• The modification or removal of data in the database
• The reading of data stored in the database

SELECT Count(*) FROM Band;

SELECT AVG(NumberOfMembers) FROM Band;

SELECT SUM(NumberOfMembers) FROM Band;

SELECT VenueName, Date 
FROM Booking 
WHERE Band-Booking.BookingID = Booking.BookingID AND Band-Booking.BandName = 'ComputerKidz';

SELECT table1.column1, table2.column2... 
FROM table1 
INNER JOIN table2 
ON table1.common _ field = table2.common _ field;

UPDATE Band SET NumberOfMembers = 6 WHERE BandName = 'ComputerKidz';

DELETE FROM Band-Booking WHERE BandName = 'ITWizz'; DELETE FROM Band WHERE BandName = 'ITWizz';