Introduction to Relational Databases and MySQL

1. Introduction to Relational Databasesand MySQL Lecturer: Jerry Smallwood Email: ubaceg4@dcs.bbk.ac.uk Introduction to Database Technology

2. DT-Sat Class Schedule • Sessions 1 & 2: Sat 14th November • Sessions 3 & 4: Sat 21st November • TMA Self-Study Session: Sat 28th November • Sessions 5 & 6: Sat 5th December (TMA Due) • Sessions 7 & 8: Saturday 12th December • FMA Due: 2pm on Monday 11th January 2010 Introduction to Database Technology

3. DT Assessment (2009/10) TMA (Tutor Marked Assignment) • The TMA carries 25% of the total marks • requires you to produce a design for a database for Miles Better, a small car hire company • TMA Self-Study Session: Wed 11th November • TMA Submission Deadline: 6pm on Wed 18th November FMA (Final Module Assignment) • FMA carries 75% of the total marks • requires you to create the Miles Better car hire database that you designed for the TMA • FMA Submission Deadline: 2pm on Monday 11th January 2010 Introduction to Database Technology

4. ITApps Assessment (2009/10) ITApps Assessment Process (2009/10) • Full details on the ITApps website:http://www.dcs.bbk.ac.uk/itapps/assessment.html • Note: Late submissions is possible but at a penalty (mark capped at 40% of total marks for that assignment) • Note: note issue of ‘mitigating circumstances’ Chair ITApps/FdScIT Exam Board • Ian Harrison, email: i.harrison@bbk.ac.uk • Contact re. ‘mitigating circumstances’ or any other issues that prevent you submitting the TMA/FMA on time. • Note: Lecturers cannot grant extensions to deadlines Introduction to Database Technology

5. Session 1 - Overview • Introduces the basic concept of a database and shows why it is useful. • Introduces the Yum Juices case study that will be used throughout this module. • Introduces you to MySQL, the Open Source Relational Database Management System that you will use to study this module. Introduction to Database Technology

6. What is a database? • an organized body of related informationwordnet.princeton.edu/perl/webwn • A collection of data organized for rapid search and retrieval by a computer.www.clock.org/~jss/glossary/d.html • A collection of related data stored in one or more computerized files in a manner that can be accessed by users or computer programs via a database management system.sparc.airtime.co.uk/users/wysywig/gloss.htm • A database collects information into an electronic file, for example a list of customer addresses and associated orders. Each item is usually called a ‘record’ and the items can be sorted and accessed in many different ways.www.webmotion.co.uk/resources/d.php • A database is an electronic filing collection of information that is organized so that it can easily be accessed, managed, and updated.www.salvagedata.com/hard-drive-recovery-terms/ • A set of related files that is created and managed by a database management system (DBMS).www.bradycommunications.com/perspectives/glossary.aspx • A collection of information stored in one central location. Many times, this is the source from which information is pulled to display products or information dynamically on a website.www.gravitatedesign.com/glossary.htm • Relational data structure used to store, query, and retrieve information.fwie.fw.vt.edu/tws-gis/glossary.htm Source: Google “Define: database” Introduction to Database Technology

7. A database consists of… • A collection of data • Software to store, access and manage the data (DBMS) User B User A DBMS Collection of Data Introduction to Database Technology

8. Advantages 1 • Data independence • Changes to structure of the data do not affect programs used to access it • Consistency of data • Each item only recorded once. No danger of its being updated in one place but not another • Reduced redundancy • Each item of data is only stored in a single place. No duplication of data • Data integrity • Control and check data entry • Data security • Restrict access to authorised users Introduction to Database Technology

9. Advantages 2 • Centralised control • Database administrator will control who has access to the data • More information • Users have access to wider range of data that might previously have been held in different places and possibly on incompatible systems • Greater productivity • The DBMS query language allows users to design their own queries and get an immediate response Introduction to Database Technology

10. Disadvantages • Cost • Database systems are complex, difficult, and time-consuming to design. • Initial training required for all programmers and users. • Hardware and software start-up costs. • Potential loss of data through system failure • Need for backup and recovery procedures • Essential to ensure that no data is lost • Security • Data may get into the wrong hands • Privacy • The increased use of databases means a large amount of personal information is stored in many different places Introduction to Database Technology

11. Data Storage • Data is stored in tables • Each row is a record • Each column is an item of data (field) within the record Introduction to Database Technology

12. Types of Database • Flat file • All data in a single table • You can use a spreadsheet such as Excel to create a database of this type • Relational • Data split into related tables, linked by keys Introduction to Database Technology

13. Flat File Database – example 1 Yum has stored details of customers and the orders they have placed in a single table. What problems might this cause? What happens when they also record details of the items included in each order? Introduction to Database Technology

14. Flat File Database – example 2 Multiple items in each order Table needs a record for each order item Even more duplication of data! Introduction to Database Technology

15. Relational Database • Data is split into entities • An entity is something that the database stores information about • A noun • A person, place, thing, event • Data for each entity is stored in a separate table (known as a relation) • What are the entities in the previous example? Introduction to Database Technology

16. Yum Database Entities • Each of the following entities will be stored in a table in the Yum database: • Customers • Orders • Order Items • Juices Introduction to Database Technology

17. Attributes • An attribute is a property of an entity • Each attribute is a column in the table for its associated entity • Each row in the table is a record. The attributes are the fields in the record • What are the attributes for the customer entity in the Yum database? • Customer ID • Title • First name • Surname • … Introduction to Database Technology

18. Primary Key • A field (or fields) that uniquely identifies each record in a table • All tables in a relational database should have a primary key • What would be a suitable primary key for the customer table in the Yum database? Introduction to Database Technology

19. Foreign key • Used to relate one entity to another to allow the database to retrieve related data • The primary key of the parent table is stored in the related record of the child table Introduction to Database Technology

20. Foreign key - example • To relate customers to the orders they have placed we store the customer ID of the customer placing the order in the order table • Customer ID is the primary key of the customer table and a foreign key in the order table • Each customer ID will be recorded just once in the customer table but may be recorded many times in the order table Introduction to Database Technology

21. Customer and Order Tables in the Yum Database Primary key Foreign key What is the name of the customer who placed order number 1? What are the numbers of the orders placed by Bo Peep? Introduction to Database Technology

22. Database Terminology • Entity • Attribute • Record (row) • Column or Field • Table or Relation • Primary Key • Foreign Key Introduction to Database Technology

23. Now do… • Hands-on Exercise 1 • Explore the Yum Juices sample data Introduction to Database Technology

24. Relational Database Management System (RDBMS) • Software to manage data stored in related tables • Examples: • MySQL • Access • Oracle • SQL Server Introduction to Database Technology

25. SQL • Structured Query Language • Query language for accessing and modifying data in relational databases • Used by all RDBMSs • Data Definition • Store data • Tables • Data Manipulation • Retrieve information • Queries Introduction to Database Technology

26. MySQL is… An Open Source Relational Database Management System (RDBMS) Introduction to Database Technology

27. Open Source Software • Free to use, modify and redistribute • Source code available for modification by users • Developed collaboratively Introduction to Database Technology

28. Finding out about MySQL The MySQL website: http://www.mysql.com/ The Reference Manual Introduction to Database Technology

29. Using MySQL for this Module • MySQL is a client/server system • Your database is stored on the School of Computer Science MySQL server mysqlsrv • A database has been created for you as you do not have sufficient permissions to create a database on this server • You use the telnet Internet Protocol to connect to the server • You need a username and password from your tutor before you can do this Introduction to Database Technology

30. Now do… • Hands-on Exercise 2: • Connect to your database • Use basic SQL commands Make sure you have got your MySQL username and password from your tutor. Introduction to Database Technology

31. Review Basic SQL Commands SHOW DATABASES USE database_name SHOW TABLES CREATE TABLE SHOW COLUMNS INSERT INTO table_name UPDATE table_name SELECT * FROM table_name DELETE FROM table_name DROP table_name Introduction to Database Technology