m1g505190 introduction to database development 2. creating a database
TRANSCRIPT
M1G505190
Introduction to Database Development
2. Creating a Database
The database schema
This representation of data model in a database is sometimes referred to as the database schema
Schema defines: the tables the fields in each table the relationships between tables
Once the schema has been designed, it can be implemented in the chosen RDBMS
Introduction to Database Development 2. Creating a database #2
Logical and physical design
logical design the process which deals with gathering business
requirements and converting those requirements into a model which is not specific to any particular RDBMS
physical design the process of designing a schema which can be
implemented in a specific RDBMS
Introduction to Database Development 2. Creating a database #3
Representing classes and attributes
The following class diagram shows the User entity in the GCUTours data model
Introduction to Database Development 2. Creating a database #4
-name-address-username-password-datejoined
Userclass nameclass name
attributesattributes
Representing classes and attributes
How do we represent entities in a database? We need to design a database table for
each class A table is a set of data that is organized in
horizontal rows and vertical columns The columns are identified by names – based
on attributes of class Columns in a data table are often called
fields
Introduction to Database Development 2. Creating a database #5
Representing classes and attributes
We need to design a table for the User class. Designing a table requires you to:
provide a name for the table provide a name and data type for each field in
the table
Introduction to Database Development 2. Creating a database #6
Different names for the same thing
Database designers often seem to have several words for the same thing, like column and field
Here is a list of some (nearly) equivalent database terms: Table = Relation (in fact, a relational database is
a database made up of relations) Column = Field Row = Record = Tuple
Introduction to Database Development 2. Creating a database #7
Naming tables
It’s quite common to give database tables plural names
We’ll call the table Users You don’t have to use this naming
convention, but it is a useful way of making sure you know when you’re talking about the class (User) and when you mean the database table (Users)
Introduction to Database Development 2. Creating a database #8
Defining the fields
Each attribute in the class becomes a field Need to specify the type of data to be held For example:
name field needs to hold a piece of text address needs to hold text, and may need more
characters than name datejoined needs to represent a date
Introduction to Database Development 2. Creating a database #9
Defining the fields
Most RDBMSs use data types similar to standard SQL data types
Standards defined by ANSI and ISO Some variation between different systems Vendors don’t stick strictly to standards
Introduction to Database Development 2. Creating a database #10
Some common data types
Introduction to Database Development 2. Creating a database #11
Defining the fields
Possible fields for Users table : name – nvarchar, field size=25 address – nvarchar, field size= 100 username – nvarchar, field size=10 password – nvarchar, field size=10 datejoined – datetime
Introduction to Database Development 2. Creating a database #12
The Users table
Introduction to Database Development 2. Creating a database #13
field namesfield names
row of data for each userrow of data for each user
•Table represents a class in the data model
•Each row represents an object, or instance of the class
Implementing the table in WebMatrix
Definition View define fields
Can also use SQL
Data View view and enter data
Introduction to Database Development 2. Creating a database #14
Why are data types important?
Advantages of using well-chosen data types: Constraining data
Only allow valid numbers, dates, etc. Ordering
Allow data to be sorted in numerical order Calculations
Number or date calculations will only work properly if the data type is correct
Introduction to Database Development 2. Creating a database #15
Additional constraints
Default values Check constraints, for example:
Value must be in a certain range Value must be from a predefined list of possible
values
Introduction to Database Development 2. Creating a database #
Choosing the right fields
With the right choices, you can make it much easier to find and use the data
For example, we may have a need to sort or search for users by last name
Should really split name into two separate fields, firstname and lastname
Introduction to Database Development 2. Creating a database #17
Implementing a table in SQL
All RDBMSs allow you to create a table and define its fields using an SQL statement
Worth learning SQL: you can then work with any RDBMs in the same
way enterprise applications use SQL to work with
database WebMatrix allows you to create and execute
SQL statements
Introduction to Database Development 2. Creating a database #18
SQL Create Table example
Introduction to Database Development 2. Creating a database #19
Inserting data
Datasheet view User-friendly data entry forms SQL INSERT INTO statement
Introduction to Database Development 2. Creating a database #20
SQL INSERT INTO example
Introduction to Database Development 2. Creating a database #21
or, if values for all fields in table are present, in correct order:
NULL values
If we miss out some of the fields when inserting a new row then the values are left empty, or NULL
Can also set NULL values explicitly to get same result:
Introduction to Database Development 2. Creating a database #22
NOT NULL constraint Often you don’t want to allow a field to
ever be left empty You probably want to make sure, for
example, that every user has a password
Introduction to Database Development 2. Creating a database #23
in CREATE TABLE statement:
Primary keys
What do you think will happen if two users with the same name are added to the Users table?
We need to be able to tell them apart in some way, otherwise we may end up booking a holiday for the wrong person.
It is almost always necessary to ensure that every row in a table is uniquely identified
Introduction to Database Development 2. Creating a database #24
Primary keys
This is done by defining a primary key for each table
A primary key is a field, or a combination of fields, that is guaranteed to have a unique value for every row in the table
The database will not allow new row to be added if the value or values of its primary key match a row which already exists in the table
Introduction to Database Development 2. Creating a database #25
Choosing the primary key
What about firstname? No, it is likely that there will be users with the
same first name The same applies to lastname
We could use (firstname, lastname) there could be several users with the same full
name What about (firstname, lastname, address)?
what if there are a father and son with the same name, living at the same address?
Introduction to Database Development 2. Creating a database #26
Choosing the primary key
Better choices: the username field, so that every user needs to
have a unique username add an ID field to the table specifically for the
purpose of identifying each row username is a good choice here However, many tables don’t have an obvious
candidate like this, and adding an ID field is then the best choice e.g. order numbers, account numbers, etc.
Introduction to Database Development 2. Creating a database #27
Defining the primary key
Introduction to Database Development 2. Creating a database #28
Data type for primary key
Fields of any data type can be used for the primary key
It is common to use either integer or text fields.
Most RDBMSs have a special data type intended for ID fields
In SQL Server it is called Identity
Introduction to Database Development 2. Creating a database #29
Identity
Identity field needs to be of an integer type int, bigint
Set automatically by the database when a new row of data is added to a table
The value set is always unique One more than the largest value already
inserted in the database Values in deleted rows are not re-used
Introduction to Database Development 2. Creating a database #30
Creating an identity field
Introduction to Database Development 2. Creating a database #
Default values and constraints
Introduction to Database Development 2. Creating a database #
Representing relationships
Entities in a data model rarely exist by themselves
The entities in the GCUTours class diagram are all related to other entities
For example bookings are related to tours and to users
Introduction to Database Development 2. Creating a database #33
Referential integrity
It wouldn’t make sense to create a booking for a tour that didn’t exist, or for a user that didn’t exist
Defining relationships in a database can prevent such invalid data from being stored
This is known as enforcing referential integrity
Introduction to Database Development 2. Creating a database #34
Types of relationships
Relationships are defined in the data model as associations of these types: one-to-one
fairly uncommon one-to-many
very common many-to-many
Need to represent relationships in the database schema
Introduction to Database Development 2. Creating a database #35
Representing one-to-many
Firstly, need two tables with primary keys:
Introduction to Database Development 2. Creating a database #36
-location-name-description-adultprice-childprice-departure
Package
-departuredate-offer
Tour
1..1 0..*
The foreign key
There is nothing yet in either table to say which tours are connected to which packages
We need to add a field to one table which will make that connection
This field will be a foreign key field
Introduction to Database Development 2. Creating a database #37
Which table?
Which table should contain the foreign key field?
Generally, in a one-to-many relationship, the foreign key field will be in the table at the ‘many’ end of the relationship
In this example, this is the Tours table
Introduction to Database Development 2. Creating a database #38
What’s contained?
What should the foreign key in Tours contain? Needs to be some value that uniquely
identifies a row in the Packages table. That is exactly what the primary key of
Packages does. As a general rule:
the foreign key field of a row in one table should contain a value matching the value of the primary key field in one row of the related table
Introduction to Database Development 2. Creating a database #39
The foreign key in Tours
Tours should have a field matching the packageID field in Packages
In this example, the foreign key field is called packageID and is of type int
This matches the data in the primary key field in Packages
Introduction to Database Development 2. Creating a database #40
Relationship between Tours and Packages
Introduction to Database Development 2. Creating a database #41
Referential integrity in this example
The foreign key makes sure that we can’t create a tour for a package that doesn’t exist
If we tried to insert a new row in Tours with packageID = 20, for example, the database wouldn’t allow it
Because there is no matching row in Packages
Introduction to Database Development 2. Creating a database #42
Foreign keys and NULL
What if we insert a row into Tours with a NULL in packageID?
Foreign key relationship only ensures that we can’t have a value that doesn’t exist in the other table
If we want to make sure that any new tour MUST match an existing package, then we can define the packageID field in Tours to be NOT NULL
Introduction to Database Development 2. Creating a database #43
Defining relationships
SQL, in CREATE TABLE statement
Using WebMatrix New Relationship window
Introduction to Database Development 2. Creating a database #44
need to define fieldand define foreign key
need to define fieldand define foreign key
Relationship with multiple fields
What if the primary key of the related table contains more than one field?
In Packages could have
The foreign key in Tours would need to reference both fields
Introduction to Database Development 2. Creating a database #45
Representing one-to-one
A one-to-one relationship can often be implemented with a single table
Fields representing the attributes of both entities
Introduction to Database Development 2. Creating a database #46
-employeeID-firstname-lastname
Employee
-NatInsNumber-salarygrade
PayDetail
1 1
a PayDetail belongs exclusively to one Employee, who can only have one PayDetaila PayDetail belongs exclusively to one Employee, who can only have one PayDetail
Another one-to-one example
These entities are clearly separate Can be implemented with foreign key
Introduction to Database Development 2. Creating a database #47
-departmentName-manager
Department-employeeID-firstname-lastname
Employee
1 1
Each Department has one manager who isAn Employee. One Employee can only be manager of one Department
Each Department has one manager who isAn Employee. One Employee can only be manager of one Department
Another one-to-one example
Need foreign key field to be unique Each employee can manage only one
department Make manager primary key of Departments
Or make manager unique
Introduction to Database Development 2. Creating a database #
Representing many-to-many
Relational databases do not allow direct many-to-many relationships
Using foreign keys would not work – a foreign key can only match one value in a related table.
Introduction to Database Development 2. Creating a database #49
-firstname-lastname-address-username-password-datejoined
User
-title-description
MailingList
0..* 0..*
Each User can be subscribed to many MailingListsEach MailingList can have many Users subscribed to it
Each User can be subscribed to many MailingListsEach MailingList can have many Users subscribed to it
Additional table to representmany-to-many
We need to represent the relationship by creating an additional table
Both entities have a one-to-many relationship with this table
In this case the additional table could be called Subscriptions
Each row would represent the subscription of one user to one mailing list
Introduction to Database Development 2. Creating a database #50
Many-to-many example
Subscriptions has a foreign key field username which matches the username field in Users
Subscriptions has a foreign key field mailinglistID which matches the mailinglistID field which is the primary key of Mailinglists
Introduction to Database Development 2. Creating a database #51
Many-to-many example
The primary key of the Subscriptions table should be (username, mailinglist)
Each user should be subscribed only once to a particular mailing list
Each username can be in the table several times
So can each mailinglistID However, each combination is unique
Introduction to Database Development 2. Creating a database #52
Many-to-many example
Introduction to Database Development 2. Creating a database #53
Many-to-many example
Introduction to Database Development 2. Creating a database #54
Many-to-many example
Introduction to Database Development 2. Creating a database #55
Summary of GCUTours schema
Packages(packageID, location, name, description, adultprice, childprice, departure)
Tours(tourID, departuredate, offer, packageID) Users(firstname, lastname, address, username,
password, datejoined) Bookings(bookingID, tourID, username, adults,
children, status) MailingLists(mailinglistID, title, description) Subscriptions(username, mailinglistID,
subscriptiondate)
Introduction to Database Development 2. Creating a database #56
Different ways of representing relationships
In an enterprise system, the same data model may be represented in different ways in different parts of the system
Relational database and Java classes deal with relationships in very different ways: relational database – uses matching foreign and
primary key fields Java classes – use object references, with no
need for matching text or numeric fields
Introduction to Database Development 2. Creating a database #57
Altering a table
Sometimes you need to change the design of a table after it has been created, or even after it has data in it
Here are a few examples:
Introduction to Database Development 2. Creating a database #58
Define primary key after table createdDefine primary key after table created
RDBMS may not allowyou to drop fields with data in them
RDBMS may not allowyou to drop fields with data in them
New field – existing rows will have NULL values
New field – existing rows will have NULL values