Information system paper
Databases and Data Management
1
Learning Objectives
A student who successfully completes this section will be able to:
Explain the purposes of databases.
Describe the types of database management techniques.
Understand big data.
Identify crucial elements of a database.
Understand Normalization.
Write a simple SQL Query.
Above is the list of objectives for this lecture.
2
1) Persistent Storage
Data is retained in the absence of power.
Week 5 – Databases and Data Management
Purposes of a Database
In this Von Neumann architecture, the devices that relate specifically to Data Management are circled in red. Persistence is a necessary characteristic of data. Databases are the mechanism for holding items of information that need to be remembered after the program ends – even after the computer is turned off.
3
Week 5 – Databases and Data Management
Purposes of a Database – Operational Databases (OLTP)
Operational databases, used everywhere in companies, organizations, and institutions, are the mechanism for Online Transaction Processing (OLTP) scenarios. That is, situations where there is a need to collect, modify, and maintain data on a daily basis. The type of data stored in an operational database is dynamic, meaning that it changes constantly and always reflects up-to-the-minute information. Organizations such as retail stores, manufacturing companies, hospitals and clinics, and publishing houses use operational databases because their data are in a constant state of flux.
4
Week 5 – Databases and Data Management
Purposes of a Database – Analytical Databases – OLAP
In contrast, analytical databases are primarily used in online analytical processing (OLAP) scenarios, where there is a need to store and track historical and time-dependent data. An analytical database is a valuable asset when there is a need to track trends, view statistical data over a long period of time, and make tactical or strategic business projections. This type of database stores static data, meaning that the data are never (or very rarely) modified. The information gleaned from an analytical database reflects a point-in-time snapshot of the data. Chemical labs, geological companies, and marketing analysis firms are examples of organizations that use analytical databases. Analytical databases often use data from operational databases as their main data source, so there can be some amount of association between them; nevertheless, operational and analytical databases fulfill very specific types of data processing needs and creating their structures requires radically different design methodologies.
5
Week 5 – Databases and Data Management
Purposes of a Database – Data Warehousing
Data warehousing is the process of transforming transient transactional data into long-term storage data. Data go through a process of extraction from the transactional databases in an organization, combination with other data sources, and storage in a format that suits the purpose of the warehouse: speed of access, particular demographical representation, or archival storage. These data warehouses can become enormous, which is one of the reasons why designing for big and archival data storage is very different from designing for quick update of operational data. This big data has always been important for archival purposes, but the advent of the Internet added a whole new, much bigger, dimension of usefulness. When you type a key word or phrase into your browser’s search engine, you essentially are hoping to be presented with the opportunity to see practically everything that has ever been published about that key word or phrase. Think of the size of the databases that hold this data and the complexity of crawling them to find those tidbits!
6
Week 5 – Databases and Data Management
Purposes of a Database – Distributed Database
Distributed storage of data was always important for big companies, but in today’s global business environment, it is more important than ever. The trick to distributed data is figuring out the design for breaking up the data for storage, transmission and update, that best suits the company’s purposes.
7
Week 5 – Databases and Data Management
Some Database Models:
1) The Hierarchical Database Model
I would like to talk about some of the many database storage models that exist.
The hierarchical database model was created by IBM in the 60’s. Data fields in this type of database were structured hierarchically and the database was typically diagrammed as an inverted tree. A single table in the database acted as the “root” of the inverted tree and other tables acted as the branches flowing from the root.
A relationship in a hierarchical database is represented by the term parent/child. In this type of relationship, a parent table can be associated with one or more child tables, but a single child table can be associated with only one parent table.
In order to retrieve data from this type of database, the whole tree needs to be traversed starting from the root node.
8
Week 5 – Databases and Data Management
Some Database Models:
The Network Database Model
The network database was, for the most part, developed as an attempt to address some of the problems of the hierarchical database. The structure of a network database was represented in terms of nodes and set structures.
9
Week 5 – Databases and Data Management
Some Database Models:
The Relational Database Model
The relational database model was presented in June 1970 by Dr. Edgar F. Codd, an IBM research scientist, in the paper “A Relational Model of Data for Large Shared Databanks”.
The following quote from Don Chamberlin, a co-inventor of SQL, is a great discussion of the difference in this new model. Here is the quote: “Ted’s basic idea was that relationships between data items should be based on the item’s values, and not on separately specified linking or nesting. This notion greatly simplified the specification of queries and allowed unprecedented flexibility to exploit existing data sets in new ways. He believed that computer users should be able to work at a more natural-language level and not be concerned about the details of where or how the data were stored.”
10
Week 5 – Databases and Data Management – The Relational Database Model
The point of the Relational Database Model is that complexity is managed and redundancy is eliminated through the use of key fields. These are fields in the data table that can be used to connect records in multiple tables. The key fields hold unique identifiers for the entities in the records. In situations where there is a one-to-many relationship of records in the tables, tables in the “one” side of the relationship only need to contain one record, and the unique identifier. Tables on the “many” side of the relationship can contain as many records as necessary, and each record contains the unique identifier, so that each record on the “many” side of the relationship can easily be connected to the record on the “one” side of the relationship. This saves massive amounts of storage space, and creates a more robust database by eliminating the dangers of redundant data.
11
Week 5 – Databases and Data Management – The NoSQL Family
NoSQL data management formats are departures from the relational database format that has so dominated data processing in the past couple of decades. NoSQL stands for “Not SQL” or “Not Only SQL”. These data management formats are called NoSQL because you need a different scheme from Structured Query Language to access data stored using these formats.
Many of the NoSQL databases are accessible in a RESTful format. REST stands for REpresentational State Transfer Most commonly, the database connection is made through a URI, and the queries and commands are HTTP calls.
12
Week 5 – Databases and Data Management – The NoSQL Family
Some Database Models:
4) The Document Database Model
The Document database model involves a collection of data storage formats, document-like in nature. In the example above, each record is treated as a separate structure, delimited by curly brackets, called a “document.” Document databases are fast because you aren’t dealing with the relationship layer.
13
Week 5 – Databases and Data Management – The NoSQL Family
Some Database Models:
5) The Graph Database Model
The graph database model is just what it sounds like: Data are stored in graph format with nodes, edges and properties. The Nodes represent entities such as people, businesses, accounts, or any other item to be tracked. They are equivalent to the record, relation, or row in a relational database, or the document in a document database.
Edges, also called graphs or relationships, are the lines that connect nodes to other nodes; they represent the relationship between them.
14
Week 5 – Databases and Data Management – The NoSQL Family
Some Database Models:
5) The Graph Database Model
This slide shows the nodes in a graph database with properties. Properties are the information related to each node that we want to store. They are equivalent to fields in a relational database.
You can see the potential advantage of this type of structure in modern social networking applications like LinkedIn.
15
Week 5 – Databases and Data Management – The NoSQL Family
Some Database Models:
6 and 7) The Key Value and Wide Column Database Model
What follows are some examples of Key-Value and Wide Column data storage.
16
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – .xlsx
17
Excel spreadsheets are actually a type of Wide Column store. Different software packages and protocols use different mechanisms for data storage. They look very different and were created for different purposes. On the next slides I will show you some of them. I will start with the proprietary Excel data storage format, xlsx. When data are stored using this format, and the file is opened using the proprietary software Excel, the data look like this slide.
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – .xlsx
18
If you open the .xlsx file with a text editor, you can see that the file is encoded. You cannot find my values in there or use them easily.
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – .csv
19
I saved the same file as a .csv format. csv stands for “comma-separated values”. The file is a text file which can be opened and viewed with a text editor. It isn’t completely unformatted, as you can see here. The individual cells are separated by commas in the file and the end of each line of data in the Excel spread sheet results in a new line in the .csv file. The .csv format is a common one that is produced by many types of software, and an application like Excel can open this type of file and load it into the proper place in spread sheet cells.
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – .txt
20
This time I saved the file as a Unicode .txt file. Again, Excel adds some formatting. This time the data are delimited with spaces, not commas, and again, the end of the data in the spreadsheet row is passed into the text file as a line feed.
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – .silk
21
Take a look at the next two slides. This is the same file saved in the .slk format. SYLK is a format created by Microsoft for data exchange among its applications. In this readable ASCII file, you find not only the data, but all of the instructions to recreate the Excel spreadsheet.
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – .silk
22
{“status”:”OK”,”responseTime”:175,”message”:[],”Results”:[{“geographyName”:”NATIONAL”,”landArea”:3856162.93520000,”population”:318634850,”households”:136714122,”raceWhite”:0.70490,”raceBlack”:0.11310,”raceHispanic”:0.13640,”raceAsian”:0.04040,”raceNativeAmerican”:0.00500,”incomeBelowPoverty”:0.15820,”medianIncome”:58283.05750,”incomeLessThan25″:0.24080,”incomeBetween25to50″:0.29970,”incomeBetween50to100″:0.30640,”incomeBetween100to200″:0.16480,”incomeGreater200″:0.04210,”educationHighSchoolGraduate”:0.80000,”educationBachelorOrGreater”:0.24770,”ageUnder5″:0.05160,”ageBetween5to19″:0.20450,”ageBetween20to34″:0.19750,”ageBetween35to59″:0.33600,”ageGreaterThan60″:0.21040,”myAreaIndicator”:false}]}
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – JSON
JSON and XML are types of key-value database. JSON stands for “JavaScript Object Notation”. This is a different data set, because Excel doesn’t support JSON.
23
<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?><Response status=”OK” responseTime=”166″><Results myAreaIndicator=”false”><landArea>3856162.93520000</landArea><population>318634850</population><households>136714122</households><raceWhite>0.70490</raceWhite><raceBlack>0.11310</raceBlack><raceHispanic>0.13640</raceHispanic><raceAsian>0.04040</raceAsian><raceNativeAmerican>0.00500</raceNativeAmerican><incomeBelowPoverty>0.15820</incomeBelowPoverty><medianIncome>58283.05750</medianIncome><incomeLessThan25>0.24080</incomeLessThan25><incomeBetween25to50>0.29970</incomeBetween25to50><incomeBetween50to100>0.30640</incomeBetween50to100><incomeBetween100to200>0.16480</incomeBetween100to200><incomeGreater200>0.04210</incomeGreater200><educationHighSchoolGraduate>0.80000</educationHighSchoolGraduate><educationBachelorOrGreater>0.24770</educationBachelorOrGreater><ageUnder5>0.05160</ageUnder5><ageBetween5to19>0.20450</ageBetween5to19><ageBetween20to34>0.19750</ageBetween20to34><ageBetween35to59>0.33600</ageBetween35to59><ageGreaterThan60>0.21040</ageGreaterThan60><geographyName>NATIONAL</geographyName></Results></Response>
Week 5 – Databases and Data Management
Types of Data – Data Storage Formats – xml
This is the same data set in a .xml format. XML stands for Extensible Markup Language. This format is used extensively to share data on the World Wide Web.
24
Week 5 – Databases and Data Management –
Some Database Models:
The Consistency, Availability and Partition Tolerance (CAP) theorem: When working in distributed systems, you can only have two of the three guarantees (the C, the A or the P), so you have to pick what’s important
C – Consistency: If Consistency is the most critical, then you need to go with a relational database. A system is consistent if an update is applied to all relevant nodes at the same logical time. A common example of where Consistency would be the most important guarantee is in a banking application or perhaps one that runs a nuclear facility. In these scenarios, it’s critical that every single piece of data is accounted for at every moment. Therefore, you’ll probably want a relational database with a high level of control over its transactions.
A – Availability: If it is more important that some version of the data is available, and not the end of the world if the data are completely current, a different format may be faster.
How do you decide which management format to use? The CAP theorem might help you with the decision process. This theory, also named Brewer’s theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the three guarantees.
25
Week 5 – Databases and Data Management –
Some Database Models:
The Consistency, Availability and Partition Tolerance (CAP) theorem: When working in distributed systems, you can only have two of the three guarantees (the C, the A or the P), so you have to pick what’s important
P – Partition Tolerance: Partition Tolerance means that the database is designed so that the system will still work if communication between two nodes is not available.
26
Some Examples of Data Types Used in PC Systems
Object The pointer to the first byte of an object
Week 5 – Databases and Data Management
Data Types
Data types are about how the data are stored in the computer. Storing data as text can save large amounts of storage space, because each character that you want to store uses only 8 bits. So the text value “32” takes only 16 bits, or two bites, to store. However, a numeric data type has to be big enough to accommodate the largest number that the type is designed to hold, so storing even a small numerical value in that data type takes the maximum number of bytes. On the other hand, text data types that look like numbers must go through some additional processing in order to be used in mathematical or arithmetic operations. Other considerations in choosing a data type can have to do with the formatting of the output of the data.
27
Week 5 – Databases and Data Management
Types of Data – Data Types
28
This is a picture of the Microsoft Access Database Management System data table building screen. When you are building a table, for each field name you enter, you must choose a data type for that field. The system will be guided by this in deciding how to store the data. The screen will allow you to add additional instructions about the data, such as validation constraints, and field size in number of bytes if the field is a string field.
Week 5 – Databases and Data Management
The Relational Database Management System
Parts of a Database Management System
A Database Management System contains tools for doing most of what you may need to do with data in your database. These elements include:
1) Data Tables: Store data about an entity type
- a) Fields: Store an individual element of data about an entity
- b) Records: Store data about one unit of an entity type
- c) Keys: Elements that relate data tables
- d) Relationships: The connection between related data tables
2) Queries: Mechanisms to access subsets of fields and
records of the database)
3) Reports: Mechanisms to output the data in a desired
format. Can be based on tables or queries.
4) Automation: Small customized programs, or macros, that do jobs not specifically provided in the DBMS tools
29
Week 5 – Databases and Data Management
The Relational Database Management System
Parts of a Data Table
Record
Field
This is a close-up of a Field and a Record in the Data Table.
30
Week 5 – Databases and Data Management – Keys and Relationships
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 1363-1369). Pearson Education. Kindle Edition.
A primary key is a field or group of fields that uniquely identifies each record within a table; a primary key composed of two or more fields is known as a composite primary key. It is the most important key in the table. Its value identifies a specific record throughout the entire database.
The primary key enforces table-level integrity and helps establish relationships with other tables in the database.
31
Week 5 – Databases and Data Management – Keys and Relationships`
.
When you determine that two tables bear a relationship to each other, you typically establish the relationship by taking a copy of the primary key from the first table and incorporating it into the structure of the second table, where it becomes a foreign key. The name “foreign key” is derived from the fact that the second table already has a primary key of its own, and the primary key you are introducing from the first table is “foreign” to the second table. Besides helping to establish relationships between pairs of tables, foreign keys also help implement and ensure relationship-level integrity. This means that the records in both tables will always be properly related because the values of a foreign key must match existing values of the primary key to which it refers.
32
Week 5 – Databases and Data Management – Types of Relationships
One-to-One
There are different relationships that two tables can bear to one another. Here are some of them listed:
One-to-One:
A pair of tables bears a one-to-one relationship when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table. In this type of relationship, one table serves as a “parent” table and the other serves as a “child” table. You establish the relationship by taking a copy of the parent table’s primary key and incorporating it within the structure of the child table, where it becomes a foreign key. This is a special type of relationship because it is the only one in which both tables may actually share the same primary key.
33
Week 5 – Databases and Data Management – Types of Relationships
One-to-Many
One-to-Many:
A one-to-many relationship exists between a pair of tables when a single record in the first table can be related to many records in the second table, but a single record in the second table can be related to only one record in the first table. (The parent/child model I used to describe a one-to-one relationship works here as well. In this case, the table on the “one” side of the relationship is the parent table, and the table on the “many” side is the child table.) You establish a one-to-many relationship by taking a copy of the parent table’s primary key and incorporating it within the structure of the child table, where it becomes a foreign key.
34
Week 5 – Databases and Data Management – Types of Relationships
Many-to-Many
Many-to-Many:
A pair of tables bears a many-to-many relationship when a single record in the first table can be related to many records in the second table and a single record in the second table can be related to many records in the first table. You establish this relationship with a linking table.
35
Week 5 – Databases and Data Management – Types of Relationships
Many-to-Many
Week 5 – Databases and Data Management
The Drexel University Database
Let’s play with these ideas a little bit.
In order to have a relational database which properly stores the data to make this report, there are some steps you have to take. First, you determine what fields you will need to store the data.
37
Week 5 – Databases and Data Management
The Drexel University Database – Normalization
Looking at the report, I came up with the list above. It would be a serious mistake to design the database with all of these fields in one table. Can you see why?
38
Week 5 – Databases and Data Management
The Drexel University Database Exercise – Normalization
Let me reiterate:
In order to design the database so that there is no redundancy, you have to break the tables down so that the “one” part of a “one to many” relationship is always in a different table from the “many” part. For example, you only want to store the Program Name in one record – definitely not in every record belonging to every student who is in the program. To do this would be not only extremely wasteful of resources, but actually dangerous to the integrity of your data. If you make a change to the program name, you only want to have to update one record, not thousands of them!
The process of breaking the database down into tables in this way is called Normalization.
39
Week 5 – Databases and Data Management
The Drexel University Database Exercise – Structured Query Language
SELECT Campus.Campus_ID, Campus.Campus_Name, Campus.Campus_Address, Campus.Campus_City, Campus.Campus_State, Campus.Campus_Zip, Campus.Campus_Phone
FROM Campus
WHERE (((Campus.Campus_City)=”Philadelphia”))
ORDER BY Campus.Campus_Name;
You retrieve data in a relational database by using Structured Query Language (SQL) Sometimes it is also pronounced “Sequel”. SQL is the standard language used to create, modify, maintain, and query relational databases. This slide shows a sample SQL query statement that you can use to produce a list of all Drexel campuses in Philadelphia, sorted by campus name. The “WHERE” statement specifies filtering criteria for the query. The “ORDER BY” statement specifies sorting order.
40
Week 5 – Databases and Data Management
The Drexel University Database Exercise – Structured Query Language
SELECT Campus.Campus_ID, Campus.Campus_Name, College.College_ID, College.College_Name, College.Dean
FROM Campus INNER JOIN College ON Campus.[Campus_ID] = College.[Campus_ID];
This SQL command uses the relational nature of the database. It selects data from two different tables, College and Campus, that are related on Campus_ID.
41
Week 5 – Databases and Data Management
Careers in Database Management – Database Administrator
What do you like to do? There are many areas of employment in the database field, that cover a variety of skills and interests. The next few slides display some ideas about things you can do if you want to work with data.
42
Week 5 – Databases and Data Management
Careers in Database Management – Analyst
43
Week 5 – Databases and Data Management
Careers in Database Management – Programmer
44
Week 5 – Databases and Data Management
Careers in Database Management – Web Designer
45
Week 5 – Databases and Data Management
Careers in Database Management – Data Mining
I worked with databases for many years, and found it to be a particularly satisfying way to spend my time. Maybe you would like to go that way as well. Think it over and have a great week!
46
References
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 1411-1452). Pearson Education. Kindle Edition.
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Pearson Education. Kindle Edition.
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 666-668 AND 674-676). Pearson Education. Kindle Edition.
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 644-648). Pearson Education. Kindle Edition.
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 649-657). Pearson Education. Kindle Edition.
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 714-717). Pearson Education. Kindle Edition.
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Location 749). Pearson Education. Kindle Edition.
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 1375-1384). Pearson Education. Kindle Edition
Hernandez, Michael J.. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Kindle Locations 776-781). Pearson Education. Kindle Edition.
47
References
https://www.bing.com/images/search?view=detailV2&ccid=rkmTW%2fJu&id=B12D6EBE32A13700CD6675EA160E168452B940BA&thid=OIP.rkmTW_JuVcJORNF0AhdJtAEsDT&q=edgar+f.+Codd&simid=608045814420737545&selectedIndex=17&ajaxhist=0
https://www.w3schools.com/sql/sql_quickref.asp
https://www.lib.umn.edu/copyright/using-images-teaching
https://www.stinkyinkshop.co.uk/articles/ultimate-guide-to-images
https://www-03.ibm.com/ibm/history/exhibits/builders/builders_codd.html
https://www.bing.com/images/search?view=detailV2&ccid=rkmTW%2fJu&id=B12D6EBE32A13700CD6675EA160E168452B940BA&thid=OIP.rkmTW_JuVcJORNF0AhdJtAEsDT&q=edgar+f.+Codd&simid=608045814420737545&selectedIndex=17&ajaxhist=0
http://ds.iexpertify.com/wp-content/uploads/2012/12/OLAP.jpg
http://www.busitelce.com/images/14articles/olap-system.png
https://www-03.ibm.com/ibm/history/exhibits/builders/builders_codd.html
https://www.w3schools.com/sql/sql_quickref.asp
http://datawarehouse4u.info/images/data_warehouse_architecture.jpg
http://content.provideocoalition.com/uploads/distributed_database.png
https://msdn.microsoft.com/en-us/magazine/hh547103.aspx
http://searchdatamanagement.techtarget.com/definition/NoSQL-Not-Only-SQL
48
References
https://www.bing.com/images/search?view=detailV2&ccid=wH5X%2FnSI&id=DFC38D19CF8CAE0EBA2EA4799BBF6C80C4E16E8C&thid=OIP.wH5X_nSInYhr9LTB106YGAEsCm&q=document+database+model+images&simid=608041807573485742&mode=overlay&first=1
http://savas.me/wp-content/uploads/2013/03/image5.png
http://www.c-sharpcorner.com/UploadFile/f0b2ed/introduction-of-nosql-database/Images/NoSQL%20database.jpg
https://www.bing.com/images/search?view=detailV2&ccid=Nl65vjVZ&id=489DE78B9B70C1BC23198F62A41751E2B6F4C8B6&thid=OIP.Nl65vjVZhnUgKWDvslhK4AEsDv&q=key+value+nosql+data+stores&simid=608018378526428515&selectedindex=11&mode=overlay&first=1
https://s-media-cache-ak0.pinimg.com/736x/d5/f9/77/d5f97710a2a62e832e7eb98b3be8c63c.jpg
http://vccsystem.eu/wp-content/uploads/2014/04/Administrator-baz-danych_small.jpg
https://image.freepik.com/free-vector/web-programmer_23-2147502079.jpg
http://www.the9gag.com/images/pictuers/4am_programmer_room.jpg
49
