SUPER-DUPER-IMPORTANT DATABASE DOCUMENT …with …



centercenterFigure 1 Meerkats!SUPER-DUPER-IMPORTANT DATABASE DOCUMENT …with the meerkats on it.Note: Meerkat content is strictly limited to this title page… …Unless we think more meerkat content will facilitate more learning…Did you knowYoung meerkats don’t cry wolf, unlike young vervet monkeys who cry ‘cheetah’ alarm at everything until they are ignored by group members. Meerkats have many different calls for the different types of dangers and are very effective communicators. Why is it important that they have different calls for different dangers?9410077300Figure 1 Meerkats!SUPER-DUPER-IMPORTANT DATABASE DOCUMENT …with the meerkats on it.Note: Meerkat content is strictly limited to this title page… …Unless we think more meerkat content will facilitate more learning…Did you knowYoung meerkats don’t cry wolf, unlike young vervet monkeys who cry ‘cheetah’ alarm at everything until they are ignored by group members. Meerkats have many different calls for the different types of dangers and are very effective communicators. Why is it important that they have different calls for different dangers?A bit about the hopes and dreams of this document…This document wants to be the ultimate cheat sheet, the best beginner’s resource ever, the place that’s so clean, clear and comprehensive for your early databasing needs that you come HERE before you go to Google. Pretty ambitious… but possible!This document is a living document, and it belongs to you the students. This is just the beginning, we’re going to constantly add to it, edit it, and throw paint on it. The original will always be available if something horrific occurs, and if we are vigilant with our versions we can always recover backups as required. You are regularly backing up, right?Document Version DetailsDocument Filepath:T:\Az\Dip Web\Week 7\Super-Duper-Database-Document_v1.12.docxLast Updated: DATE \@ "dddd, d MMMM yyyy" Monday, 14 March 2016Last Updated By:deSilva, AzhiraiCreated By:Az de SilvaDate Created: CREATEDATE \@ "dddd, d MMMM yyyy" \* MERGEFORMAT Tuesday, 8 March 2016Version Number:1Table of Contents TOC \o "1-3" \h \z \u A bit about the hopes and dreams of this document… PAGEREF _Toc445743604 \h 1Document Version Details PAGEREF _Toc445743605 \h 1Designing a Database PAGEREF _Toc445743606 \h 3The Design Process PAGEREF _Toc445743607 \h 3What do we need? PAGEREF _Toc445743608 \h 4Stop for a moment… PAGEREF _Toc445743609 \h 4Gather Requirements PAGEREF _Toc445743610 \h 4Conceptual Diagram – Entity Relationship Diagram (ERD) PAGEREF _Toc445743611 \h 4Cardinality PAGEREF _Toc445743612 \h 5Data Analysis for Logical Design PAGEREF _Toc445743613 \h 5Normalisation PAGEREF _Toc445743614 \h 5Category Tables PAGEREF _Toc445743615 \h 5Data Types PAGEREF _Toc445743616 \h 6Data Dictionary PAGEREF _Toc445743617 \h 6Naming Convention PAGEREF _Toc445743618 \h 7Data Modelling PAGEREF _Toc445743619 \h 7Data Modelling Environments PAGEREF _Toc445743620 \h 7Security PAGEREF _Toc445743621 \h 8Database Threats PAGEREF _Toc445743622 \h 8Security Plan PAGEREF _Toc445743623 \h 9The Gleaning PAGEREF _Toc445743624 \h 9Which Database Management System? PAGEREF _Toc445743625 \h 10Installing UniServer PAGEREF _Toc445743626 \h 10Starting Your Servers PAGEREF _Toc445743627 \h 10Creating a database PAGEREF _Toc445743628 \h 11Console Access PAGEREF _Toc445743629 \h 11UniServer Access PAGEREF _Toc445743630 \h 11phpMyAdmin Access PAGEREF _Toc445743631 \h 12Create a Database PAGEREF _Toc445743632 \h 12Create Users PAGEREF _Toc445743633 \h 12Migrating Data PAGEREF _Toc445743634 \h 14Sample Data PAGEREF _Toc445743635 \h 14Creating a Spreadsheet for Export as CSV PAGEREF _Toc445743636 \h 14Check It! PAGEREF _Toc445743637 \h 15First Test PAGEREF _Toc445743638 \h 15Revision – Key Terms PAGEREF _Toc445743639 \h 18Hey what’s a database again? PAGEREF _Toc445743640 \h 18References PAGEREF _Toc445743641 \h 19Database Designright23025700Designing a DatabaseWhy does it matter? What do you think? Let’s discuss and write it here:45210071975650Figure 2 Masterful Architecture Design0Figure 2 Masterful Architecture Designleft90805Designing the database…Clear and concise planLess likely to screw it upFuture-proof-ish that can evolve with the websiteDocumented client requirementsEveryone on the same page (team, client)Legal sign off on requirements protects all parties00Designing the database…Clear and concise planLess likely to screw it upFuture-proof-ish that can evolve with the websiteDocumented client requirementsEveryone on the same page (team, client)Legal sign off on requirements protects all partiesCommit this to a memorable place in your brain-notes: -= Best practice is to design the database first! =-The Design ProcessWhat do we need? What or who is the database for? Microsoft has some very good (if old) documentation that concisely describes how to go about planning a database. Basically we need to follow these steps:Gather information from stakeholders.Investigate what is required. This might mean talking to the relevant stakeholders (anyone involved or who has a stake in the project/product), conducting interviews, taking copies of relevant documentation (product lists, customer records etc.).Identify the entities/objects/items required.These are the entities to be managed by the database. ‘Entity’ describes the objects that are eventually destined to become tables. They can represent tangible things like customers or intangible things like orders.Design the entities/objects/items in a data model.This can be done on paper, in Visio, in MS Access, in specialised data modelling environments. Identify the relationships between entities.How do the entities relate to one another, what ordinality do they have?Stop for a moment…Are these in the right order? Is there any back and forth between these steps? What if later down the track, despite an excellent design and implementation, more entities are discovered? How likely is it that this happens?Gather RequirementsUsing the scenario provided, as a class extract all the requirements for this database. What does the database need to store, and what does it need to do?StorageFunctionalityCelestial bodiesName, type etcAdd new celestial bodiesEdit celestial bodiesDelete celestial bodiesView celestial bodiesUsersUsername, password, access levels (e.g. admin, companion, other)Add new usersEdit usersView usersBeingsFirst name, last name, origin, race, companion? etcAdd new beingsEdit beingsView beingsAdventuresName of adventure, date, companions, beings, celestial body, location etcAdd new adventuresEdit adventuresView adventuresConceptual Diagram – Entity Relationship Diagram (ERD) As a class, after discussing the given scenario and extracting from it all the database requirements, collaborate to create a conceptual diagram on the board, showing the required entities, their attributes and their relationships.2305050621665Figure 3 High Level ERD with two Entities0Figure 3 High Level ERD with two Entities CardinalityCard-what? Cardinality is the degree of uniqueness of a value in a column of a table. Does it occur zero, one or many times? Zero or One cardinality (high cardinality) has a high degree of uniqueness, the lower the cardinality the lower the degree of uniqueness (more occurrences of the same value).In data modelling, cardinality is often represented by CrowsFoot notation, or in UML modelling numbers are used to represent the cardinality. For our diagrams, we will be using CrowsFoot notation (as picture above in Figure 3).Data Analysis for Logical DesignNormalisationData normalisation is the process of organising the data within the database in such a way as to reduce redundancy. What is data redundancy? Why does it matter? According to Wikipedia on databases:“Data redundancy leads to data anomalies and corruption and generally should be avoided by design; applying database normalization prevents redundancy and makes the best possible usage of storage. At the same time, proper use of foreign keys can minimize data redundancy and chance of destructive anomalies.”So what exactly is normalisation, why do it? Microsoft gives a good summary:“Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database. What is an "inconsistent dependency"? While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee's salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken. There are a few rules for database normalization. Each rule is called a "normal form." If the first rule is observed, the database is said to be in "first normal form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.”Normalisation goes all the way to the fifth form, but we will usually only be normalising to the third form. Reviewing our conceptual model, how can we improve upon or, normalise, our current database design?Category TablesCategory tables list the categories that can be assigned to an entity and provide a flexible way to avoid data redundancy in the database. Looking at our scenario’s ERD, how can we normalise with category tables? Look for repeating values, is there data that occurs or has the chance to occur more than once? Identify what category table needs to be made and update the current ERD to match our normalisation(s) based on the data provided below in Table 1.Table 1 Sample Celestial Body DataCelestial BodyTypeDetailsEarthPlanetMass: 5.972 × 10^24 kg (597200000000000000000000000)Population: 7.125 billion (7,125,000,000)Surface Area: 510.1 million km? (510,100,000)Description: Earth is the third planet from Sol, the densest planet in the Sol Solar System, the largest of the Sol System's four terrestrial planets.MoonMoonMass: 7.34767309 × 10^22 kg (7347670900000000000000000000000)Population: 0Surface Area: 37.9 million km?Description: The Moon is Earth's only natural satellite. It is one of the largest natural satellites in the Sol System, and, among planetary satellites, the largest relative to the size of the planet it orbits.SolStarMass: 1.989 × 10^30 kgPopulation: 0Surface Area: 6.09×1012 km2Description: The Sun is the star at the centre of the Sol Solar System and is by far the most important source of energy for life on Earth.Demons RunAsteroidMass: 3500000 kgPopulation: 350Surface Area: 520000?km?Description: Alliance Military BaseDagmar ClusterAsteroid BeltMass: 10 × 10^24?kgPopulation: 7.125?billionSurface Area: 835 million?km?Description: Two and a half galaxies from Mutter's Spiral.Data TypesConsider the data in the table above; what kind of data types can you identify? On what basis do you decide how to type the data? Discuss all the possible options and evaluate which one is most appropriate for each field.Below is an extract from the numeric types section in the manual at , a wealth of information about all things MySQL that makes for a good starting point when looking for information.“MySQL supports all standard SQL numeric data types. These types include the exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER...”Consider carefully what data types you will apply, especially when dealing with numbers where precision calculations with exact values are required. Some questions to be considered about the data include:Are integer data types required, or decimals?How accurate do the numbers need to be?Do data comparisons need to be made?Will mathematical calculations be performed?Is this data likely to change in size, grow or shrink over time?Data DictionaryA data dictionary is usually a document that contains a list of all tables in a database, including (but not limited to) the names and types of each field in all the tables. The purpose of the dictionary is to have a centralised, organised repository of the data that is clear and accessible to all involved. Wikipedia states that “database users and application developers can benefit from an authoritative data dictionary document that catalogues the organization, contents, and conventions of one or more databases.”Naming ConventionWhat is the point of a naming convention? Is it important? A naming convention is…To organise data, make it easier to read and identify in the code. To be consistent and make it easier to work with e.g. predicting names. Easier working with teams on the same data set if everyone is on the same page.A naming convention is…To organise data, make it easier to read and identify in the code. To be consistent and make it easier to work with e.g. predicting names. Easier working with teams on the same data set if everyone is on the same page.What naming convention should we use with this scenario? Write the rules here:Naming Rules:Databases – Related to what it’s holding, lower case, underscore instead of spaces, no special characters e.g. tardis_baseTables – Related to what it’s holding, plural names, lower case, underscore instead of spaces, no special charactersFields – Related to what it’s holding, singular names, lower case, underscore instead of spaces, no special charactersNaming Rules:Databases – Related to what it’s holding, lower case, underscore instead of spaces, no special characters e.g. tardis_baseTables – Related to what it’s holding, plural names, lower case, underscore instead of spaces, no special charactersFields – Related to what it’s holding, singular names, lower case, underscore instead of spaces, no special charactersFill out the table below, to start a data dictionary for the table that will eventually hold the above data.TableP/F KeyField NameData TypeField SizeNotesData ModellingNow that we have our conceptual diagram and our data dictionary listed all the tables in our normalised database, it’s time to update to a new version of our ERD, called a Database Management System Model (DBMS Model). This DBMS Model will be a lot more detailed than our initial high-level ERD, containing information that – with the right tool - can be directly translated and imported into our database later.Data Modelling EnvironmentsThese environments enable you to visually model your database design using tools that allow you to create universally understandable designs (like Entity Relationship Diagrams (ERD)) that can be read by other trained human beings.There are lots of data modelling environments to choose from, some are free, some are online, some come built in with database server. We will be using Vertabelo, once the class list is confirmed invitations will be sent out for you all to sign up for your free student accounts.It’s worth looking into alternatives, such as:Microsoft MySQL WorkbenchMicrosoft SQL Server Database Design ToolsMost of these modelling environments aren’t free, but usually offer some kind of trial. It’s worth having a play around with a few different ones to get a feel for not only what’s available but what works best for you.Things to consider when choosing a modelling environment:How easy is it to use?How much is it?Does it have all the features you need?Can you easily export the data into the formats you need? E.g. picture files, data files.Can you collaborate and share your work with others?SecurityBelow are some questions to consider, discuss and hammer out:What is this security thing we keep hearing about? Why is security important?How do you secure a database? How do people get into databases?What is a security plan?Database ThreatsKnow your weaknesses! There are many different types of threats; it’s your job to understand, anticipate and prevent them wherever possible:Default or weak passwordsWhat constitutes a strong password?SQL injectionsAny dynamic SQL statement that uses un-validated end-user string input can be exploited by this type of SQL injection attack. Read more…Excessive user and/or group privilegesWhen users (or applications) are granted database privileges that exceed the requirements of their job function, these privileges may be used to gain access to confidential information. Read more…Unnecessary DBMS features enabledThe more services running on an operating system, the more ports will be left open, thus leaving more open doors for malicious users to abuse. Read more…Buffer overflowsA buffer overflow occurs when a program or process tries to store more data in a buffer (temporary data storage area) than it was intended to hold. Read more…Privilege escalationA network intrusion that takes advantage of programming errors or design flaws to grant the attacker elevated access to the network and its associated data and applications. Read more…Denial of service (DoS attack)So good even they are even in the movies… what are they?Un-patched RDBMSWhat, you don’t want to patch those back doors?Unencrypted dataWe got it AND we can read it!Unprotected backups or NO backups!According to the 2012 data breach investigations report published by Verizon Business, 96% of records breached in 2011 were taken from database servers. Of these, 55% exploited default or guessable credentials and 40% the use of stolen login credentials – how crazy easy is that?Security PlanHave no doubt: Prevention is always the best and first solution. Microsoft recommends these Best Practices to keep your database healthy and secure. Oracle also has an excellent chapter on security.The depth of information in a security plan relates directly to the complexity and requirements of the database. Your security plan should address all the threats listed above and include a detailed listing of users with their access privileges. Remember the Principle of Least Privilege: “A user (or process) should have the lowest level of privilege required to perform his assigned task”.The GleaningBefore we go any further, it’s good to have a read around the internet about the dos and don’ts of database design. It’s hard when you first start out because there is a lot of information, however after reading from a variety of different sources, you’ll start to see common threads between them and begin to glean an understanding of what’s commonly thought a good or bad idea.Of course, nothing beats personal experience, but here is some of the best advice I’ve found accumulated in one helpful article: Some Really Really Good Advice from Robert SheldonThoughts so farAny notes or comments we need to add or discuss?Enter thoughts here…Enter thoughts here…Which Database Management System?There are many Apache/MySQL/PHP Servers freely available to try and it’s a good idea to explore a couple of different AMPs like:WAMP (or MAMP for Mac)XAMPPLAMPYou can read more about them here on Wikipedia. Az’s first choice for learning and sandpits is still UniServer for its superior portability and simple, uncomplicated interface. They keep their Promises. (UniServer appears to also tolerate a lot more unsafe sudden shutdowns by forgetful students and their teachers.)Download UniServer now!4646092230353Figure 4 Contents of UniServer FolderFigure 4 Contents of UniServer FolderInstalling UniServerAnything you want to know about UniServer (if it exists) can usually be found either on the UniServer Wiki or in the UniServer community forum. An introduction and installation guide to the server can be found here: basic steps are: Run the downloaded UniServer executable.When it asks where to extract, ideally the root of your flash drive.Once it’s finished extracting/installing, open the UniServer folder and run the UniController.exe to launch the UniServer console (pictured on right in Figure 2).If successful, you should now see the UniServer console as shown in Figure 5 below.401698512700Figure 5 Initial UniServer ConsoleFigure 5 Initial UniServer ConsoleStarting Your ServersThere are two servers you need to run in order to run your web application: The Apache Server and the MySQL Server. Click the Start Apache button, wait for it to go green then click the Start MySQL button and wait for it to go green.4060876633425Figure 6 Servers Started on UniServer ConsoleFigure 6 Servers Started on UniServer ConsoleIf there are any problems getting the servers started, UniServer normally produces a pop-up message telling you what the problem is. Common problems with getting Apache running frequently include port conflicts and instance conflicts:A port conflict means there is another application using the port Apache wishes to use, this is often (but not limited to) Skype, so it’s a good idea to check out what the program is and terminate it.An instance conflict means that there is already an instance of the apache server running and this instance must be terminated in order to proceed with a new instance. Best way to do this is to open Task Manager (right-click on the Taskbar) locate it in processes and end the task/process.Creating a databaseThere are two-point-five ways we could go about doing this, firstly is the age-old MySQL console, and secondly is the GUI console e.g. phpMyAdmin or the UniServer MySQL options. There are pro’s and con’s for each, and it boils down mostly to convenience and class time. If you want to become intimate with SQL, the console is a sure path there. As we have less and less class time, and the MySQL console is far from an easy, convenient path, we will be using phpMyAdmin (although I will introduce you to the limited options available from UniServer also).Console Access44037252730500To access the MySQL Console you must have MySQL running – on the UniServer dashboard this is obvious by the green square indicating it’s on. To launch the console, simply click on its button (highlighted in yellow in Figure 7 on the right) in the MySQL Utilities section of the UniServer dashboard.From the UniServer MySQL Console we have full access to the MySQL server, we can manipulate databases, tables, data, everything.462216539370Figure 7 MySQL Console Button00Figure 7 MySQL Console ButtonThe MySQL Console should appear as shown in Figure 8 below:Figure 8 MySQL Console3797300397510Figure 9 UniServer MySQL Menu Options00Figure 9 UniServer MySQL Menu OptionsUniServer AccessAs shown in Figure 9, through the UniServer MySQL menu we can do the following:Create and delete databasesCreate, edit and delete database usersBackup and restore a databaseRestore default user passwordView MySQL error logphpMyAdmin AccessHave we been here before? :D To get to phpMyAdmin the simplest way is from the UniServer Dashboard, there is a button to open phpMyAdmin in the MySQL Utilities panel on the main screen (shown below with blue focus):Create a DatabaseOnce we’re in there we need to go ahead and create a database, by this stage you should know what you’re going to name your database.Create UsersFrom the menu bar in phpMyAdmin we can also create and administer users, as shown in yellow highlight below:Add the user details according to your technical specifications:Select the appropriate privileges according to your technical specifications:Migrating DataEven if there is no existing system from which to migrate the data, the data still needs to be assembled in some way and then entered into the database. Frequently data comes in a comma separated value format (.csv file) which is a format that can be easily exported from a spreadsheet application, such as Microsoft Excel. MySQL Workbench has a very easy to use Wizard for importing and exporting data (see here), but unfortunately for us we don’t have access to that product for this session. We going to go the long, hard road :DSample DataIt’s a good idea to have a simple project and some data handy for experimenting and practicing. Table 2 below contains an example of some data as you might receive it from a client. Later down the track we will go into how to import masses of data that you wouldn’t even remotely contemplate manually entering.Table SEQ Table \* ARABIC 2 Sample Client DataProduct TypeProduct NameProduct DescriptionDVDLittle Shop of HorrorsSinging and dancingAudiobookHow to stay calmSelf-sedation at a low low cost!Blu-rayJurassic WorldNo one ever learns!Blu-rayStar Wars: The Force AwakensHungry, for money :DCDBest of Pearl JamWouldn’t fit on one CD…Creating a Spreadsheet for Export as CSV273240552641500Using our sample data from above, we’re going to go through all the steps right through from accepted for example a table of products in an MS Word document, all the way through to getting those products into your MySQL database. Open Microsoft Excel and, copying the above table, paste it into a new spreadsheet as raw data (we do not want to carry across any of the formatting). With any luck it should look something like this:Figure 10 Excel data sample IIf you imagine this as a table, what’s missing? How do we uniquely identify each product? :D Exactly, we need an extra column, an ID column. Cell 1 should have the text ‘id’, the cells below should number 1-5:Figure 11 Excel data sample IISave this file as “mediabuster.xlsx” to a new folder called “MediaBuster”.Now Save As a new file “mediabuster.csv” (comma delimited) in your “MediaBuster” folder:Figure 12 Save As CSV FileCheck It!Never trust or assume that something just worked, always check what has been created! Open up your mediabuster.csv file in NotePad++ and visually check that it’s in the right format for import:Figure 13 Double Check in NotePad++Do those table headings in line 1 look right to you when you imagine a products table? Are they following your naming convention for the project? Better to make the changes/fixes now, in this easy editing environment =)Consider also any illegal characters… are there any adverse effects lurking in wait?First TestIt’s always best to do a simple trial run the first time you attempt something. Perform the following steps:Create a new database called ‘mediabuster’.Select the desired .csv file, in this case mediabuster.csv as the file the import: Select the format CSV.Tick the last two options as shown:Hit go, and with any luck you should get a success message:Figure 14 Successful import of CSV file to table.Do you believe it? Check the table in both Browse and Structure views:Figure 15 Browse View of Products TableFigure 16 Structure View of Products TableThere is a problem with the properties of the fields:While the ID came across beautifully as an INT data type, it needs to be assigned as a primary key, note that I have clicked Primary against my ID and manually turned it into the primary key.The ID also needs to be an unsigned Auto_Incremented number.While the data types for the remaining fields have been fairly well guessed as varchar’s, it estimated their sizes and these will need to be manually adjusted.Note any other settings that are no ideal or are inappropriate?377444010795Figure 17 Change the table name in OperationsFigure 17 Change the table name in OperationsWhat is the table name? What is it supposed to be? We should fix that! Can we change the table name once it’s in there? Yes :DSelect the table you wish to modified (in our case here ‘table 1’ and then go to the Operations tab in phpMyAdmin.Under Table options type in the new name for the table (e.g. products)Click Go!Check it!Revision – Key TermsHey what’s a database again?We’d better write it down any terms here just in case we forget.The Thing’s NameDefinition of the ThingDatabaseKoalas are not bears CITATION Wik16 \l 3081 (Wikipedia, 2016) they are marsupials.References BIBLIOGRAPHY Wikipedia. (2016). Koala. Retrieved from Wikipedia: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download