Lecture 4: Managing Data PDF

# Information Technology (IT) ## IT & Users - IT doesn't pay attention to the users ## Internet of things - Things that can be connected to the internet. Ex: cellphone, car, watch. - This happens without cables. ## Cloud Computing - Delivers services without the need of a physical server. - Thin...

# Information Technology (IT) ## IT & Users - IT doesn't pay attention to the users ## Internet of things - Things that can be connected to the internet. Ex: cellphone, car, watch. - This happens without cables. ## Cloud Computing - Delivers services without the need of a physical server. - Think of virtualization. - You get access to these remotely. - Ex: uwindsor email, we just need internet connection to get access to it. ## Difficulties in Managing Data (Slide 5-6) - Data increases exponentially with time - Multiple sources of data (e.g., internal, personal, external) - New sources of data (e.g., AI generated content) - Data rot (e.g., customers move to new address, change their phone numbers, etc.) ## Problems with Data - We need to constantly update the information - Data degradation: - Refers to problems with the media on which the data are stored, such as temperature, humidity, and exposure to light can cause physical problems with storage media and thus make it difficult to access the data. - Data security, quality, and integrity - This data needs a physical environment, which needs maintenance. - We need a reliable place to store the data - We want to be able to use the data at anytime - We want to make sure it is safe and secure ## The Spreadsheet Approach (Slide 8-14) - Huge numbers of orders coming in every day - Case 1: Multiple employees are trying to enter those orders into the spreadsheet you made in the first week. - Case 2: Instead of 1 spreadsheet, every employee has their own! There are certain concerns. - Break-up the spreadsheet into six separate spreadsheets so that it doesn't crash every time you try to enter an order. - Benefits: It helped prevent Excel from crashing. - Concerns: - Each spreadsheet is still very large and it takes a while to open each one. - Every time a customer calls to ask a question about an order or an invoice, It takes time before the employee can find the correct spreadsheet. - The more annoying part is that multiple people are trying to use the spreadsheet at the same time. So when a customer calls, an employee has to wait until somebody else finishes before he/she can open the spreadsheet. - Case 3: To move the excel sheet to the CLOUD! Now multiple employees can work on it, BUT there are still concerns. - Benefits: Multiple people edit the spreadsheet at the same time. 😀 - Concerns: - The spreadsheets are still pretty big. So they still take a while to open and search. - Sometimes multiple people end up interacting with a spreadsheet at the same time, and they end up writing over each other's entries. - A lot of inconsistencies arise in the data which are hard to catch if you don't have multiple spreadsheets open at once. ## The Database Approach - A database is a shared and organized collection of interrelated data, designed to meet the needs of multiple types of end-users. - An efficient, reliable, and safe, multi-user storage that gives access to massive amounts of persistent data. - Stored in a standardized, convenient form. | Unit Price | Total Purchase (Date | Contact Person | Phone | Email | |---|---|---|---|---| | 5.99 | 5990 | 3/2/2015 | Trudy | [email protected] | | 6.99 | 3495 | 3/2/2015 | Renee Marrs | [email protected] | | 7.99 | 1598 | 3/2/2015 | James Craig | [email protected] | | 399.5 | 3/2/2015 | Kelly Novak | (919) 504-2948 | Kelly [email protected] | | 399.5 | 3/2/2015 | Trudy | (919) 555-0177 | com Steve | | 5.99 | 5990 | 3/3/2015 | (984) 555-0177 | om | | 7.99 | 599.25 | 3/16/2015 | Maria | (919) 644-2665 | [email protected] | | 7.99 | 799 | 3/18/2015 | Km Wills | (919) 985-6515 | [email protected] | | 5.99 | 5990 | 3/22/2015 | Renee Barrs | (919) 837-8116 | [email protected]. | | 7.99 | 399.5 | 3/25/2015 | Tm Surles | (919) 236-0428 | [email protected] | | 5.99 | 11980 | 3/28/2015 | Tudy | (919) 555-0177 | [email protected] | | 7.99 | 399.5 | 3/28/2015 | Lucy Baker | (919) 641-2212 | [email protected] | ## Why Database? - Minimize: - Data redundancy: the same data are no longer stored in multiple locations. It won't allow you to repeat the same thing 2x. - Data inconsistency: Single version of the truth. - Maximize: - Data security: Having high-security measures in place that ban malicious access to it. - Data integrity: Data meet certain constraints (e.g., ensuring no alphabetic characters in a Social Security Number field) - Data independence: Applications and data are independent of one another; that is, applications and data are not linked to each other, so all applications are able to access the same data. ## How to design & Build Database? - DBMS (Database Management System) - automated tool use to create a physical design of a database. - Ex: SQL Server, MySQL, Postgres SQL - All DBMS have "relational databases". - Relational database- break down datasets into smaller data sets or tables (relations). - Such subsets of data is in the form of tables (relations) representing entities with primary/foreign keys representing relationships. | Columns in Original Spreadsheet | Tables in Relational Database | |---|---| | Order No. | Order No. | | Customer | CustomerID | | Invoice No. | Invoice No. | | Beans Purchased (lbs) | Beans Purchased (lbs) | | Unit Price | Price | | Total Purchase ($) | | | Date | Date | | Contact Person | ContactID | | Phone | | | Email | | | Loyalty Discount | Loyalty DiscountID| | Distribution Location | Distribution LocationID | | Egger's Employee | Employee (took order) | | Egger's Employee Phone | | | Egger's Employee Sale No. | | - Relation: - A two-dimensional table, with the following properties: - Each column has a distinct name (attribute name). - The order of columns is immaterial. - Each column contains values about the same attribute. - Each row is unique/distinct, duplicate rows are not allowed. - The order of rows is immaterial. - Primary keys are not null. - Primary keys allows no redundance, they are unique identifiers to help look for something. - Ex: license plate, or uwindsor ID - It's a # that can be store & not duplicated. ## Database Development Process (Slide 30) - You need to see what info you actually need & don't. | | | |---|---| | Requirement Collection & Analysis | Questionnaire & Interview | | Conceptual Design | ER & ERR Model | | Logical Design | Relational DBs | | Physical Design | ORACLE | - To visualize we need the ERD - Entity Relationship Diagram (ERDS) - communicate the structure of the database, & it's easier for everyone to understand. - You can get information off this ERD. - This is called Conceptual Design. ## ERD Example - For primary key, you have to underline that attribute. | | | |---|---| | Classes | Courses| | Teacher ID | Course ID | | Course ID | Course ID | | Class Name | Course Title | | | Description | | | Credits | - Entity: A thing, event, or person in the organization's environment about which someone wants to collect data (e.g., Student). - Attribute: A category, characteristic or property of an entity (e.g., Student ID) - Relationship: Association between entities - Composite entity (Associative entity): Represents both an entity and relationship. Has its own properties. ## Entity Relationship Data Model - Entity -> Table - Each entity drawn in an ERD is translated into a table in the database. - A table in ERD is referred to as "relation." - "Relation" in databases is very different from "relationship." - So a "Student" entity will turn into a table named "Student" | Student | |---|---|---| | Stud ID | Name | GPA| - Attribute -> Column - Each attribute in the ERD becomes a column in the table. - Each entity (or table) in an ERD must have a unique attribute that identifies it. - Unique attribute of an entity becomes its primary key (i.e., the column that allows you to link tables together). - Primary keys are represented in an ERD by underlining the attribute. | | | | |---|---|---| | Student | ID | | | | Name | GPA | | Stud ID | 123 | Amy | 3.9 | | | 234 | Bob | 3.4 | | | 345 | Craig | NULL | ## Exercise..1 (Slide 41) - Is this a relation? | EmployeeNumber | FirstName | LastName | Department | Email | Phone | |---|---|---|---|---|---| | 100 | Jerry | Johnson | Accounting | [email protected] | 236-9987 | | 200 | Mary | Abernathy | Finance | [email protected] | 444-8898 | | 300 | Liz | Smathers | Finance | [email protected] | 777-0098 | | 400 | Tom | Caruthers | Accounting | [email protected] | 236-9987 | | 500 | Tom | Jackson | Production | [email protected] | 444-9980 | | 600 | Eleanore | Caldera | Legal | [email protected] | 767-0900 | | 700 | Richard | Bandalone | Legal | [email protected] | 767-0900 | - Problems: - There are empty spaces in primary keys. - In the email column, there are other info like Fax, which is not good.