Databases might want to use one. - Create a database Have a basic - PowerPoint PPT Presentation

8/05/18 Part 1 Part 2 Know what a database is. Learning how to use Microsoft Access , a database management system Understand why they are useful and when you Databases might want to use one. - Create a database Have a basic understanding of how the most - Add information to the database common type of databases, “ Relational - Retrieve information from the database Databases” are organized. CS111 Part 3 Lab exercises What is a database? A database is a collection of data that is organized in a systematic way. What is a The data stored in a database is generally about a single topic. For example: Database? Patients’ files in a hospital The contents of an address book A catalog of movies in a video store 1

8/05/18 Database: What Computers make large databases possible Database Digital computers have made electronic databases possible, which facilitate is collection of related data and its metadata organized in a structured format for optimized information management the storage of very large quantities of information the efficient addition , modification or removal of that information Database Management System (DBMS) is a software that enables easy creation, access, and modification of databases for rapid search and retrieval of desired information efficient and effective database management Modern society relies heavily on the electronic databases that digital computers Database System make possible. is an integrated system of hardware, software, people, procedures, and datathat define and regulate the collection, storage, management, and use of data within a database environment 6 Database Management System Database System Environment - manages interaction between end users and database § Hardware § Software - OS - DBMS - Applications § People § Procedures § Data Database Systems: Design, Implementation, & Management: Rob & Coronel Database Systems: Design, Implementation, & Management: Rob & Coronel 7 8 2

8/05/18 These websites use databases A library catalogue Online stores or auction websites Question: What websites have you visited that likely use a database? Database management systems play a central role in the vast majority of modern businesses. What is NOT a database? A bad “database” The following are collections of data, and they can be “organized” but they are not considered databases. Imagine you want to keep track of films that you have watched. a file-system (e.g. the “C: drive” on your computer) A non-database solution is to use a plain text file (or a word processing document) a word-processing document But this could lead to many problems -- especially as the size of your “database” grew … a text-file that is written and edited by hand (e.g. using notepad, emacs) The difference lies in how databases are organized. 3

8/05/18 Databases to the rescue! A bad “database” Database Management Systems (DBMS) such as Microsoft Access , MySQL , and In this format, the data is not easily searched. e.g. Oracle can help you avoid these problems (but they don’t 100% prevent them). What if you want to know which movies are directed by Ben Affleck (and not those in which he is the star)? ...they also don’t work on their own. To properly take advantage of DBMS requires It would be very difficult to transform this data into knowledge of database design ... another format, e.g. to display it on a website. In this format, it is not easy to edit the data in the … and thus, knowledge of database design is central to a whole career path. database, e.g. changing the rating from an “out of 5 Search google for “ database analyst jobs” ! stars” system to an “out of 10 stars” would involve a lot of manual labour. In this format, there is nothing that prevents errors from creeping in, e.g. the same film being added twice? Evolution of Data Models Database: Historical Roots Timeline Manual File System • to keep track of data 1970s 1980s 1990s 2000+ 1960s • used tagged file folders in a filing cabinet File-based • organized according to expected use Hierarchical e.g. file per customer Object- Network • easy to create, but hard to oriented Web-based Relational • locate data Entity-Relationship • aggregate/summarize data 4

8/05/18 Database: Historical Roots File System: Example Computerized File System • to accommodate the data growth and information need • manual file system structures were duplicated in the computer • Data Processing (DP) specialists wrote customized programs to: • write, delete, update data (i.e. management) extract and present data in various formats (i.e. report) • Database Systems: Design, Implementation, & Management: Rob & Coronel Database System vs. File System File System: Weakness Weakness “ Islands of data ” in scattered file systems. Problems Duplication - same data may be stored in multiple files Inconsistency - same data may be stored by different names in different format Rigidity - requires customized programming to implement any changes cannot do ad-hoc queries Database Systems: Design, Implementation, & Management: Rob & Coronel 19 S511 Session 2, IU-SLIS 5

8/05/18 Network Database: Example Database Design Database Systems: Design, Implementation, & Management: Rob & Coronel Relational and Non-Relational Databases Tables, Records and Fields There are two broad classes of databases, relational and non-relational . In a relational database, data is organized into tables, where: Field We are going to focus on relational databases, which were introduced in the late 1970’s and remain the most widely used approach today. Each row in a table represents a record -- a “thing” Each column in a table represents Record a field -- an “attribute” A collection of tables form a database. But this is all most easily seen with an example. Here we see a single table that contains the attributes of several students. 6

8/05/18 How do relationships work? Relationships Between Tables There are two parts to a relationship, a primary key and a foreign key . The structure of databases comes Primary Key from relationships , which are The primary key is a unique way of identifying a connections between records in record in the table different tables. Generally: Course - every table will have a primary key field - all records must have a value in the primary key field - the primary key’s value must be unique Foreign keys A foreign key refers to a primary key Question in another table. Foreign Key Foreign Key Which field makes a This creates a connection or relationship between the two records. good primary key in this table? Primary Key Primary Key 7

8/05/18 Table B Types of Relationships One to many Foreign Key There are three kinds of relationship that one can build using One record in Table A can be related to ∞ primary keys (PK) and foreign keys (FK) multiple records in Table B One to many Example: Each enrollment refers to a single one record (ie one row) in Table A can be related to multiple records in Table B student. But each student can have several enrollments. One to one one record in Table A can be related to exactly one record in Table B One record in the Students Table can be Table A related to multiple records in the Enrolments Many to many Primary Key Table (but not vice versa) multiple records in Table A can be related to multiple records in Table B 1 Many-to-many relationship Here is an example of the many-to-many relationship. One to one The roles table can be thought of as saying: One record in Table A is related to exactly “ Sarah Polley played the role of Ana in Dawn of the Dead ” one record in Table B “ Sarah Polley played the role of Elsa in Splice ” “ Ben Affleck played the role of Bruce Wayne in Batman vs. Superman ” STUDENTS PHOTOS This relationship is relatively rare, as often “ Ving Rhames played the role of Kenneth Hall in Dawn of the Dead ” 1 1 it makes more sense for Table A and Table ID (primary key) ID (primary key) B to be merged into a single table. Each actor can be associated with multiple films. Each film can be associated with multiple actors! Full Name ImageData Sometimes this relationship is used when ROLES Date of Birth some data is costly to access and less ACTORS FILMS ID Role ActorID FilmID frequently accessed. ID First Name Last Name ID Title 1 Ana Clark 1 1 1 Sarah Polley 1 Dawn of the Dead 2 Elsa 1 2 2 Ving Rhames 2 Splice 3 Bruce Wayne 3 3 3 Ben Affleck 3 Batman vs. Superman 4 Kenneth Hall 2 1 8