M1G505190 Introduction to Database Development

1. M1G505190Introduction to Database Development 1. Databases and Database Design

2. Teaching staff • Jim Paterson Room M628James.Paterson@gcu.ac.uk • Lab tutors: • June McCrae • Andreas Komninos • Morteza Zanjireh • Abbas Javed Introduction to Database Development 1. Databases and Database Design #2

3. Online resources • GCU Learn • Announcements • Assessment information • Lecture notes, lab & tutorial sheets, solutions • Links to other resources Introduction to Database Development 1. Databases and Database Design #3

4. Reading • No set text – notes will be provided • The following books may be useful additional reading: • Beginning Database Design: From Novice to Professional  (Clare Churcher, Apress, ISBN 1-59059-769-9) • Head First SQL (Lynn Beighley, O'Reilly, ISBN 0-596-52684-9) • Many other books on databases and SQL in the library Introduction to Database Development 1. Databases and Database Design #4

5. Assessment • Class test (30%) • Hand-in assignment (70%) Introduction to Database Development 1. Databases and Database Design #5

6. What is a database? • Any collection of data can be described as a database • Computerised database systems are now very commonplace • Information is stored in a database every time we: • use a bank account • book a travel ticket • make an appointment with a doctor • etc. Introduction to Database Development 1. Databases and Database Design #6

7. Database mangement systems • A database is simply the collection of data which you need to store • To actually store the data, and to do anything useful with it, you need a Database Management System (DBMS) • A DBMS controls the way the data is stored on the computer, and provides ways of getting data in and out of the system Introduction to Database Development 1. Databases and Database Design #7

8. Data models • The way in which data is organised for storage in a database is known as the data model • Early computer databases developed in the 1960’s used a hierarchical model • Similar to the way files and folders are still organised in modern computer file systems • Most data does not fit very well into a simple hierarchy Introduction to Database Development 1. Databases and Database Design #8

9. Data models • Hierarchical data • “Real-world” data – no clear hierarchy Introduction to Database Development 1. Databases and Database Design #9

10. Relational databases • Relatively complex data like this is better handled with the relational model • Devised by Edgar Codd around 1970 • Most databases nowadays are relational databases • although there are others: object databases, XML databases, “NoSQL” databases • A database management system which uses the relational model is called an RDBMS Introduction to Database Development 1. Databases and Database Design #10

11. Databases and Enterprise Information Systems Introduction to Database Development 1. Databases and Database Design #11

12. Database servers database accessed as file or through local server database accessed through network server Introduction to Database Development 1. Databases and Database Design #12

13. Popular RDBMSs • Microsoft Access • aimed at small businesses, and useful for desktop applications and systems with a small number of users • Microsoft SQL Server, Oracle, IDM DB2 • scalable and secure, and widely used by large organisations • MySQL • open-source and quite powerful, widely used in web sites • Microsoft SQL Server Compact, JavaDB, SQLite • compact DBMSs, suitable for mobile devices in particular • ...and many more Introduction to Database Development 1. Databases and Database Design #13

14. RDBMS tools • Most RDBMSs include tools to create complete application, for example: • form designers – to allow data entry forms to be created for the user interface • report designers – to present data to the user • stored procedures – to perform processing of data according to business rules Introduction to Database Development 1. Databases and Database Design #14

15. RDBMS and other tools • Can use your RDBMS and its tools for everything, or • Can use the RDBMS as a component and use other tools and programming languages to create the other components • For example the GCUTours case study: • data entry forms and reports are created as web pages • business logic uses Java Introduction to Database Development 1. Databases and Database Design #15

16. SQL – the language of relational databases • To develop applications which use relational databases you usually need to use SQL • Structured Query Language • This is the language which is used to define queries • A query is a request to a DBMS for some specific information • Relational databases are sometimes referred to as SQL databases Introduction to Database Development 1. Databases and Database Design #16

17. SQL example • SQL queries can be quite easy to understand • For example, the following query finds the last name of all the customers in a database: SELECTlastNameFROMCustomers; • SQL can also be used to add, update or delete data, and to build the database in the first place Introduction to Database Development 1. Databases and Database Design #17

18. SQL standards • SQL is supposed to be a standard language which is supported by all RDBMSs • In fact, you need to be careful because there are some important differences between the versions of SQL used by different systems • Different versions of SQL standards (SQL92, SQL99,etc.) • Different implementations by RDBMS vendors Introduction to Database Development 1. Databases and Database Design #18

19. Designing a database • A well-designed database helps to make sure that the data stored is accurate and consistent and can be retrieved easily • What do we mean by inconsistencies? • It would, for example, be inconsistent to store a booking without storing the details of the customer making the booking • With careful design, we can make sure the database won’t allow this to happen Introduction to Database Development 1. Databases and Database Design #19

20. Steps in designing a database • Determining the intended uses of the system • Creating a data model • Implementing the database Introduction to Database Development 1. Databases and Database Design #20

21. The data model • Data model = domain model classes which represent entities we need to store permanently Introduction to Database Development 1. Databases and Database Design #21

22. Data modelling techniques • We are using object-oriented techniques with UML to design our data model • There are other methods which are also commonly used in database design • One widely used method is called Entity Relationship Modelling (ERM) • Represents the data model as an Entity Relationship Diagram (ERD) Introduction to Database Development 1. Databases and Database Design #22

23. From data model to database • Need to consider how the data model can be represented in a specific RDBMS • This requires some further design • RDBMS software has specific ways of representing and enforcing the entities, attributes and relationships in the data model • For example, a data model entity is represented as a table in the relational database Introduction to Database Development 1. Databases and Database Design #23

24. Representing the data model in an RDBMS Introduction to Database Development 1. Databases and Database Design #24

25. Different representations • business layer • for example as Java classes and objects • business logic in Java methods • database layer • data is stored permanently in a database • system queries database to get data it needs to carry out a particular action • The system needs to map data from database tables to classes Introduction to Database Development 1. Databases and Database Design #25