1 / 58

M1G505190 Introduction to Database Development

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 

virginia-skinner
Download Presentation

M1G505190 Introduction to Database Development

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. M1G505190Introduction to Database Development 2. Creating a Database

  2. 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

  3. 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

  4. Representing classes and attributes • The following class diagram shows the User entity in the GCUTours data model class name attributes Introduction to Database Development 2. Creating a database #4

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. Some common data types Introduction to Database Development 2. Creating a database #11

  12. 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

  13. The Users table field names row of data for each user • Table represents a class in the data model • Each row represents an object, or instance of the class Introduction to Database Development 2. Creating a database #13

  14. Implementing the table in WebMatrix Definition View Data View view and enter data • define fields • Can also use SQL Introduction to Database Development 2. Creating a database #14

  15. 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

  16. 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 #

  17. 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

  18. 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

  19. SQL Create Table example Introduction to Database Development 2. Creating a database #19

  20. Inserting data • Datasheet view • User-friendly data entry forms • SQL INSERT INTO statement Introduction to Database Development 2. Creating a database #20

  21. SQL INSERT INTO example or, if values for all fields in table are present, in correct order: Introduction to Database Development 2. Creating a database #21

  22. 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

  23. NOT NULL constraint • Often you don’twant to allow a field to ever be left empty • You probably want to make sure, for example, that every user has a password in CREATE TABLE statement: Introduction to Database Development 2. Creating a database #23

  24. 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

  25. 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

  26. 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

  27. 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

  28. Defining the primary key Introduction to Database Development 2. Creating a database #28

  29. 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

  30. 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

  31. Creating an identity field Introduction to Database Development 2. Creating a database #

  32. Default values and constraints Introduction to Database Development 2. Creating a database #

  33. 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

  34. 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

  35. 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

  36. Representing one-to-many • Firstly, need two tables with primary keys: Introduction to Database Development 2. Creating a database #36

  37. 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

  38. 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

  39. 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 fieldof a row in one table should contain a value matching the value of the primary key fieldin one row of the related table Introduction to Database Development 2. Creating a database #39

  40. 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

  41. Relationship between Tours and Packages Introduction to Database Development 2. Creating a database #41

  42. 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

  43. 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

  44. Defining relationships need to define field and define foreign key • SQL, in CREATE TABLE statement • Using WebMatrix NewRelationship window Introduction to Database Development 2. Creating a database #44

  45. 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

  46. Representing one-to-one • A one-to-one relationship can often be implemented with a single table • Fields representing the attributes of both entities a PayDetail belongs exclusively to one Employee, who can only have one PayDetail Introduction to Database Development 2. Creating a database #46

  47. Another one-to-one example • These entities are clearly separate • Can be implemented with foreign key Each Department has one manager who is An Employee. One Employee can only be manager of one Department Introduction to Database Development 2. Creating a database #47

  48. 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 #

  49. 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. Each User can be subscribed to manyMailingLists Each MailingList can have manyUserssubscribed to it Introduction to Database Development 2. Creating a database #49

  50. 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

More Related