Database Design P1, M1 & D1 Essay

Database is a place to store lots of data, imagine it as a giant virtual filling cabinet that can be organized in many ways. There’s software called (DBMS) which stands for database management system which allows the user to manage data in database such as; create, delete or update. Entities Entities are objects with significant mass and purpose, in database entities are tables. Every table contains many fields/attributes. The user must create a table before inputting any data. When creating a table you need to define a few things, data type, fields name & a description. Fields Name This is a column heading, this will display the title for the column etc first name, last name, address. Data Type This is the content of the data & the data type decides what content can be inputted & the file size. Description This is used to explain the content & shows a list of ‘limitations’ the user can change to get a more accurate reading etc. input mask. Attributes A characteristic, for example; a field has many attributes such as data like numbers or letters. Attributes can be used as a synonym for a â€Å"field†. In DOS systems, each files has an attribute, each file has many properties that shows if the file is â€Å"read only†, visible or encrypted. Queries A query is use for searching some specific record. It allow user to set conditions and then search the record which match the conditions. User also is able to make the search mix up with other table and sort the order of the result. Reports A Report is a summary of record. Everything you plain to print should put inside. Its purpose is for user to view information quickly and easily so display the result simply and efficiently is the main point. Forms Form is used for collect and edits information and transfers them to table. It is similar to application forms that allow user to fill in or delete their data easily and it also able to let user design own user interface. User can insert as much control as they like to build the user interface beautifully. Relationships Relationship types Database relationships are very similar in that they’re associations between tables. There are three types of relationships: One-to-one: both tables of the relationship have one field that matches up, for example; a married couple that both have a child that has no relation to the other partner. This rarely ever happens in a business organization One-to-many: one-to-many is where one table has none/one or many relations with the other table’s fields but the other table has only none/one field. For example, you have only one mother, but your mother may have several children. Many-to-many: Both tables have one/many relations to each other’s fields. Usually in this instance there are usually 3 tables. Benefits The benefits of having databases online are that it’s easier & quicker to do. The amount of paper saved & room is very beneficial. Its quicker & easier for the user to find important data in the database. The user can make forms to find & read information easier. The user can set rules to make the data input more accurate & legit. You can save the information on multiple storage units (hard drives) So if the information has been tampered with, there’s always a spare copy somewhere safe. Task 2 Primary key The primary key of a relational table uniquely identifies each attribute in the table. Primary keys could be a unique attribute such as â€Å"social security number† for a person or student etc. Primary keys may consist of a single attribute or multiple attributes in combination. Primary keys connect tables together in relationships Referential integrity There is a feature with databases called relational database management systems (RDBMS’s) that prevents users or applications from entering inconsistent data. Most RDBMS’s have referential integrity rules that you can apply when you create a relationship between two entities. For example, suppose Table B has a foreign key that points to a field in Table A. Referential integrity would prevent you from adding a record to Table B that cannot be linked to Table A. In addition, if I deleted a attribute in table A, if it is linked to table B, the data that is connecting both attributes will be deleted. This is called cascading delete. Finally, the referential integrity rules could specify that whenever you modify the value of a linked field in Table A, all records in Table B that are linked to it will also be modified accordingly. This is called cascading update. D1: Analyse Potential Errors in the Design & Construction of a Database and explain how these can be avoided There are many common errors that could occur while using database, it’s important to understand how these common error happen & what causes them. Deletion of records & fields Deleting important records & fields happen quite commonly, it’s caused by the user or by accident. If this does happen its always safe to know there is a copy of the data saved somewhere else in case this happens. It’s good to have a refresh back up time for 1 hour so every hour, the data renews itself. Human error leads to corruption or loss of fields so to ensure this doesn’t happen it would be best to set administrative rights to the right users so only the right people can edit or delete fields, if the wrong people try to use the database, an error or password will be shown. Making more than 1 copy of the data is wise just in case something happens to the first copy. Incorrect data types To decrease the chances of getting incorrect data types in database, its best to put input masks on the fields. This stops incorrect data being imputed on data forms like sign-up sheets. It’s also wise to have multiple choice questions & drop down menus; this makes it more accurate to get correct data. Using drop down menus makes it easier & quicker for the user to get a positive & effective data results. By using the drop down menus & multiple choice questions, it ensures correct spelling & grammar of important information. Validation To ensure the data is imputed clean & correct you need to use validation rules. This is done by setting rules in the design view where the user can set what data can be imputed & what is invalid. An effective tool is the â€Å"limit checker† where the user can set a limit to the amount of characters that can be imputed into the database. Range checks are effective to ensure etc. birthdays, it sets the month from 1-12 & has a set range to when the  person was born etc. 1950-2014 (we are currently on 2014 so I don’t need to extend the range). Grammar checks are there to make sure the data isn’t misspelt, it also makes personal information & letters easier to produce. There’s a rule that can be set up on database that checks the inputted data for any incorrect information, if it finds something wrong, an error message will pop up & notify the user. Null Values Null values is similar to validation in where the user needs to input data & there are a set of rules to ensure the user inputs the correct/legitimate data however null values are shown when no data has been inputted into a specific textbox. An error message will pop up if no data is put into a specific field, this rule is usually put on by the user as when starting database, you get a lot of blank fields. This rule is useful for fields that need data in them such as â€Å"personal details†, â€Å"bank details† etc. If the user doesn’t type in anything into the textbox & clicks â€Å"accept† or â€Å"enter† an error message will pop up notifying the user about the null values. The boxes with errors are marked differently to the textboxes without errors, this is to clearly identify where the error is & makes its easier & quicker for the user to resolve.