MI4007Wk2_2.pdf

Business Information Management Dr. Michael P. O’Brien Module: MI4007 Week 2 (Lecture 2 of 2) 1 What is a Database? A structured collection of related data A filing cabinet, an address book, a telephone directory, a timetable, etc....

Business Information Management Dr. Michael P. O’Brien Module: MI4007 Week 2 (Lecture 2 of 2) 1 What is a Database? A structured collection of related data A filing cabinet, an address book, a telephone directory, a timetable, etc. A database system is a computer program for managing electronic databases. 2  Electronic Databases They can help to save you from this kind of work! 3 What is a DBMS? DBMS: Database Management System Collection of software for managing the database + software for organizing the data and verifying it as well as software for retrieving the data through queries as well as creating reports based on the data. Some applications include: – Telecom – Industry – Banking system – Sales – Airlines – Education sector – Online shopping 4  DBMS Software There are different DBMS software packages available: – Microsoft Access – Microsoft SQL Server – Oracle – MySQL – Corel Paradox – FoxPro – and many more….. 5 DBMS Capabilities A DBMS can: – Create / Delete tables – Modify tables add records delete records update records rearrange records change the table structure – Retrieve data from a single / multiple table(s) find and display a record answer queries – Create reports formatted displays of query results or table contents 6  Basic Database Concepts Tables, Records & Fields The basic organizational tool in the database is the table which is organized into records which are subdivided into fields Most relational databases contain many tables. For example, a database might have a Customers table, a Products table, an Orders table, and many more – hundreds, even thousands! 7 Basic Database Concepts A record contains a number of fields: A particularly important field is called a key A key is a field that uniquely identifies the record This field is called the primary key of the table. 8  What is a Relational Database? A relational database is a collection of tables from which data can be accessed in many different ways without having to reorganise the database tables. That is, once relationships are created, tables can “talk” to each other. We can link (relate) the tables to find: - Which doctors have seen a patient - Which students are in a class - Which item is selling the most on Fridays 9 Designing Databases A database needs to be reliable, consistent and have a structure that suits the data you collect. Databases are persistent, which means that the structure is fixed in place. The fields and data values are set so that it is easy to add information and build a database without changing the structure. When designing a database, it is important to decide what the structure will be before you start adding data. It is difficult to change a database structure once it has been set in place. 10  Designing Databases If data was not organised, it would be difficult to work with. For example, it is hard to make sense of this table which contains data for an address book: 11 Data Types When you create a database you need to set data types for each field. For example, in a film database you might need alphabetical characters for 'Titles', but numbers for 'Duration’. Fields are usually restricted to a certain data type. Data typing is a way of classifying data values that have common properties. Different kinds of data values also need different amounts of memory to store them and have different operations that can be performed upon them. The most commonly-supported data types are: integers (whole numbers), for example: 4, 27, 65535 floating point numbers (with decimal points, sometimes called real numbers, or floats), for example: 4.2, 27.4, 56.8 characters, for example: a, F, 3, $, £, # character strings (ordered sequences of characters), for example: abc, def456, 3erf78!@ Boolean values, for example: 'True' or 'False' 12  The Four Basic Operations in a Database Design: – Create the database by defining the tables and specifying the fields Data Entry: – Adding the actual data by hand (easy to make errors), automatically, or importing it from other files Queries: – Ask questions about the data Reports: – Producing a document with information 13 Database Options Freeware/ Microsoft Microsoft Oracle/SQL Shareware Excel Access Hire a Simplicity Basics Intermediate Advanced programmer # of Multiple of 1 1 Multiple Users Multiples Multiple No No Yes Yes datasets Always consult with your computer security team if you are Security working with any sensitive data. 14  Why Use Access? Familiar look and feel of Windows Easy to start building simple databases Can build sophisticated systems It’s already on your computer True relational database 15 Basic Design Rules Organising Data Once you’ve chosen your fields, you need to decide if they belong in different tables. Data should be kept in separate tables if you have an indeterminate number of entries. One employee can have a number of evaluations. EMPLOYEE TABLE Emp ID First_Name Last_Name Eval1 Eval2 123-456 Sallye Shapiro 15/01/2010 14/011/2011 125-985 Samuel Smith 12/01/2011 EVALUATION TABLE 248-890 Sidney Samueson Emp_ID Eval_Date 123-456 15/01/2010 123-456 14/01/2011 123-985 12/01/2011 16  Basic Design Rules No Derived Fields If a field you are not using as a link exists in another table, it should not be repeated in the current table. Listing it in both places leads to data entry errors. Since we have the Emp_ID in both tables, there is no need to include the Employee’s Last Name in the Evaluation table. EMPLOYEE TABLE EVALUATION TABLE Emp_ID First_Name Last_Name Emp_ID Last_Name Eval_Date 123-456 Sallye Shapiro 123-456 Shapiro 15/01/2010 125-985 Samuel Smith 123-456 Shapiro 14/01/2011 248-890 Sidney Samueson 123-985 Smith 12/01/2011 You can use a query to pull values from both tables into one datasheet. 17 Basic Design Rules Data is broken down into Smallest Logical Parts Each segment of data you want to sort or filter should be kept in its own field. For example, what if I needed to sort by City or Zip Code? Pulling fields together is fairly simple, pulling them apart can difficult. ID Home Address 987 123 West Main Street, Gainesville, FL 32601 654 456 South 3rd Road, Apt 12, Newberry, FL 32684 ID Addr1 Addr2 City State Zip 987 123 West Main Street Gainesville FL 32601 654 456 South 3rd Road Apt 12 Newberry FL 32684 You can join fields together in queries, forms and reports. 18  Basic Design Rules Descriptive Field Names Be careful of using too many abbreviations in your field names. You have up to 64 characters, but long field names can be difficult to use in expressions. Be Clear, Be Concise and Be Consistent. EMPLOYEE TABLE ID FN LN DOB DOH SSN CMT 1234 Sallye Shapiro 17/06/1970 02/07/2001 123-450 N/A EMPLOYEE TABLE Emp Emp_First_ Emp_Last_ Emp_Birth_ Emp_Hire_ Emp_System_ Emp_Comm _ID Name Name Date Date Signal_No ents 1234 Sallye Shapiro 17/06/1970 02/07/2001 123-450 N/A 19 Basic Design Rules Unique Field Names Often we will have the same type of data in multiple tables. IDs, Comments, First Names, Last Names could all refer to different datasets. DOCTOR TABLE PATIENT TABLE First_Name Last_Name First_Name Last_Name Sallye Shapiro Annie Adams Samuel Smith April Appleton Sidney Samueson Arnold Arlington Bobbie Brown When these two Last Name fields are pulled into Butch Bruce the same query they will appear with the table name in front of the field name: Patient Table.Last Name Doctor Table.Last Name 20  Basic Design Rules No Calculated Fields In Microsoft Excel we enter the data and create our formulas all at once. In Access you are creating a “Data” table, a table of the raw data. Export the data into Microsoft Excel AFTERWARDS for calculations, pivot tables, etc. Emp_ID Hourly_ Hours_Worked Pay Rate 123 $10.00 40 $390.00 Pt_Med_ Height Weight BMI Rec 456-456 2 91 23 Note: You can create calculated expressions in queries, forms and reports. 21 Basic Design Rules Unique Records If you don’t Last_Name UserName Phone Department have unique Smith rsmith 273-5051 Business records, your Smith rsmith 273-5051 Business database can’t tell which record Smith rsmith 273-5051 Science you may be Thomas bthomas 392-5555 IT referring to. Van Winkle sleepyguy 846-5656 Science Last_Name Emergency_Contact Smith Mary Anne Smith 22  Primary Keys LastName UserName Phone Department Smith rsmith 273-5051 Business Thomas bthomas 392-5555 IT Van Winkle sleepyguy 846-5656 Science To ensure that each record is unique in each table, we can set one field to be a Primary Key field. A Primary Key is a field that that will contain no duplicates and no blank values. Looking at the table above, what would be the best Primary Key? 23 Primary Keys LastName UserName Phone Department Smith rsmith 273-5051 Business Thomas Bthomas 392-5555 IT Van Winkle sleepyguy 846-5656 Science While each column in this particular data set has unique data, the field that will work best for us is UserName. Many employees will work for the same department, have the same last name and possibly even share telephone numbers, but each employee should have a unique UserName. When there is not a unique field in your data set, you can use an AutoNumber. Access can create incremented or random AutoNumbers for your primary key. 24  Basic Design Rules Unique Records We use the ID LastName UserName Phone Department unique primary key as our link 1 Smith rsmith 273-5051 Business between our 2 Smith rsmith 273-5051 Business tables, this helps 3 Smith rsmith 273-5051 Business ensure we 4 Thomas bthomas 392-5555 IT connect to the 5 Van Winkle sleepyguy 846-5656 Science correct record. Emp ID EmergencyContact 2 Mary Anne Smith 25 Let’s Create a Database 1. Class exercise first… 2. Microsoft Access Demonstration 26  Case Study Check out case study on Brightspace (only 1.5 pages) on how UPS competes globally with Information Technology. Have a go at answering the four questions that follow. 27 28