0% found this document useful (0 votes)
281 views23 pages

Database Notes

Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
281 views23 pages

Database Notes

Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
You are on page 1/ 23
Unit 3. Databases Introduction, Definition of terms and functions of Database Management system [Management of data is important in all organisations. Data in organisations forms part of tne stratage, tactical, ‘operational management and dacision making process. Electronic databases have changed the way datas created, managed and stored using computerised information systems. Definition of terms Database - t's a collection intarrelated data files stored in an organised manner to permit easy retrieval Database management system - Software that manages storage, processing and retrievsl of data in 8 computer. Examples of database management system software include: Fox?ro, Dbase ill, Dase IV, MYSQL, Lotus Approach, Microsoft Access Microsoft SOL server and Oracle. Fling management systems - Computerised filing method that uses nor-integrated files calle fat files. Functions of a database management sofeware ‘interfaces 2 datanase to other application programs 1 Safeguards database against unauthorized access. up Allows staustcal analysis of data 1w Permits tne adition of new records. i Allows modification of existing records. vi Organises dats for easy access, retrieval and manipulation of records. Types of database models 2) Flat Fle Holds only one set of data and it snot any different from manual files. Do not allow relationships to be created amongst fle. Nama Tompson Lucas Patent ID RBr545/2018 Date of admission zamena Block admixed c Bed number HOSPIBLOCK C1004 ) Hierarchical Model Records are arranged in a form that resembles family tr¢e with lower level records subordinating to the upper level records as shown in Figure below, The top mast record is called Roct cirectory To access lower level records, parent records are frst opened, Allows one to mary relationships eet arom —= ene eos Let ene€) Network model Recembles hierarchical but has baen modified to allow flexible access co racords. A child racors can belang to several parant records. Allows many to many relationships. [Root directory I I I Parent records |—hild records Network database model d) Relational database model Data Is organised in tables also called relations. Records are uniquely identified using a key field (primary kay) Permits several types of relationships including oneo-one, one-to-many and many-to-many relationships. Relationsnips are created amongst tables by linking a primary key to a foreign kay. Rows nold records (also called tuples) wile columns hold fields (sso called attributes} ‘Admission table + | Patient 1D NRBI3452018 J Primary key Patient name ‘Thompson Lucas Date of admission (22/7/2014 Block admitted c Bed number “HOSP/BLOCK C/004 Payment table Patient 1D_ NRBI3452018 reciente + [Amount of bill 220772014 Date paid a Balance HOSP/BLOCK C7004 Object oriented models Designed as a collection of objects An object cons'ss of both data (state) and the Instructions (oehaviour! to process the data. The process of combining state and behaviour in one object is called encapsulation, Allows databases to hold multimedia data Difference between Filing Management and Database Management Systems Fling Management Systems Database Management Systems (08M5) Use files that are not integrated. Use integrated les Low data incegrany Use nnagrated les ‘lows sower cata modtfcaton ‘lows faster oata modification Not compatible with other applications Compatible with other applicationsFeatures of a database management software DBMS nave the following features: Table - A data structure tnat's used to accept and organise records. Form — A graphical interface used to enter data onto an underlying table. Query Asool used to extract and analyse data, Report generator - Used o create and present both soft and hardcopy summaries of a database Macro - Used to automate frequently executed commands so snat they can be executed togarner Programming module - Used to add new instructions in a database Data organisation in a database Data is organised from me smallest unit called a field to the largest unit called a database as explained below: Fields Ieconsists of 2 character or sat of cnaractars mat ge some place of information about an enciy. Records It is 2 set of related fields tnat give sensible information about an entity, for examole, a patients row in a table showing patient's name, patients number, admission date, diagnosis and prescrition. Tables lt isa set of related records, for instance. a file showing all the patients admitted in a snecitic ward. Databases [cis set of interrelated files. For example, al patients, doctors and stock inventory files in s hospital Field data types Tex {includes alphabetic letters, numbers and punctuations and can support up to 255 characters. Used when a field wil not b€ mathematically manipulated. Memo I consists of letters, numbers and special characters. It supports up to # maximum of 32,000 characters. Number I consists of numeric numbers 010 9. The field can be mathamacically manipulaces. Date or Time identifies afield as ether date or time, Ie can be mathematically manipulated. Currency I identifies numeric values that have decimals parts ike monetary values ‘Auto number Ik automatically sets the frst entry to one and subsequently automatically increments the values in the field by YES or NO Itallows logical values with two trutn values like rue or false to be entered. OLE Object OLE stands for Oajact Linking and Embedding. tis used wth graphical user interface applications to insert JIimages, pictures, drawing and charts among othersField properties, Define finer details of fields. They include + Fld sie I allows tne user to ser he number of characters a feld will consist of ether than the default 50. For ‘numeric fields, ce following ses are used: + Integer and long integer ‘Accept fields witn no decimais. + Byte Accept numbers berween 0 and 255. + Single and double Accept number with decimals, Single supports up to 38 decimal places while double up to 308 decimal places. Format Dictates now data appears on tne screen wnen typed. Decimal places It allows the user to specify tne number of decimal places. Input mask I automatically formats the field entry into a specified manner. Caption I gives a mare descriptive name for the field in a table or @ form Default value Ie s a value enat aucomacically appears in che table or form ifno otner encry is made. Validation rule Logical expression tnat controls the values entered in a table fel Validation text ‘A message that appears once the validation rule is not observed Required Ic ensures that an entry's made in a fia before proceeding to the next Feld or record. Allow zero length Iallows tne user to proceed witout making an entry In te fel. Indexes {is used to organise records for easy searching. Note: Primary key is a special index used to speed up searching and sorting records in ¢ table. ft uniquely identifies each record stored in @ table so that no record duplication is made Table Design Terms Entity -A person, thing or a place about wnicn dats can be stored. ‘tribute - Date that can be used to describe an entity Foreign key - A primary key transferred to another table to help create relationships Referential integrity - A festure thet ensures that all records entered in the related table existin the primary table Normalisation - Process of removing repeating fields among relating tables. A table is created by spectfying + Fisk names + Field daca types | Flald properves Primary key ‘Table name A table can be linked to other existing table(s) in one of the following ways: + One-to-one relationship - A field in a table matches only one record in tne relating table. + One-to-many relationship ~ A field ina table matches several records in another table. « Many-to-many relationship ~ Spectic records in stable match several records in encther table,Form design [Aform is a graphical interface that enables the user to easily view and make data entries into an underlying rable or query. Terms: + Controls - Are objects placed on a form te snow data or execute seme commands. Examples include: text Box, ‘check box and command buttons among ethers. “Bound control - Data is sourced from a table or query. + Unbound control - Data is typed cirectly. Does not source data from table or query. The following form layouts exis: *Columnar Tabular «Datasheet ‘Justified Query design ‘The term query refers to a question used to instruct a database to retvieve dats or perform operations such as Insertion, update and deletion cf data in a table. Used to analyse table(s) dats using ether of the following types of query: i) Select query It allows the user to speci the search criteria Ii) Action query They are used to makes changes to records such as delete, update and acid a group of records from one table to another or create 2 new table trom another table. Four types of action Query |) Make table query- Creates 8 new table from an existing table, ii Delete query: Erases specified records from a table(s), 1i) Update - Modifies date in stable 'w)Append query: Adds data in a table from a able(s) Calculations in a query (Calculations in 2 quary can be parfarmad by using tne Total functions (Zor by creating basic formulae. & faw formulae are deccrived eelowe + Min - Returns te lowest value from a field column. + Max - Returns the highest value from a feld column. + Count- Returns the number of items in field column. + Sum ~ Adds numerical data icems. “Avg - Computes the mean of numeric data items.Specifying the search criteria Conditional staternene(s] are used in the criteria row to extract specific records, Operators and wild cards are used in specifying ene criteria racords. 0) Relational operators which include: “less thant) + greater thant) + grester than or equaltol>=) + ess than or equal tof) +s equal to¢ 1) Logical operators which include: “AND “OR + NOT “BETWEEN i) Wild cards - sre special caracters used to subsctute other characters at the'r position In searcn or querying process. They include: +7 = substitutes 2 single character atts position (Example: 2am can extract Tom, Yorn, Com) + * - substitutes group of characters atts position(Example: P* can extract Paul, Poem) + #- supstitutes any single number Example: 245 wil match 208, 215,225, 235) + []~ suostiutes any single cnaracter witnin the brackets (Example: PIAE]L matcnes PAL, PEL) substitutes any character not in brackets (Example: Gllaojt matches Gill Gut and not Good, cost) Enforcing database security Several data security tools are avalable in databases such as password protection, encryption scrambling up characters so that they cannot make sense to attacking programs unless they are decrypted), hiding database objects and user level securityKCSE Revision Questions KCSE 2012 Paper | Qn.20. a) Win the aid of a dlagram, dascrine tne nierarcnical dataoace mooel. (4 marks) KCSE 2009 Pager 1 Qn.17. d) Name anc describe any two types of database models. (6 marks) KCSE 2007 Paper 1 Qn 13. Diferendiate between the following pairs of terms #5 used in database design b) Table and query (2 marks) KCSE 2012 Paper 1 (Qn.20. )1) Name three types of validation checks during dats encry. (3 marks) KCSE 2011 Paper 1 (Qn.13, State the purpose of each of the following in database design. a) Input masé (1 mark) p) Default value (1 mark) Papert Qn.18. a) Using two examples, explain the term field properties as used in database design. b) Below is an extract from 2 hospital database table. Patient Ne Name Date Registered | Amount Paid | Remarks LDK/O01 Mathew Olang | 04/05/08 250000 | TogoferxXray LDk/908 —JoyChelmo 67/06/08 1200.00 | Medicine robe ordered LDKI008 Joy Kamau 09/08/08 350000 | Te be admited for further check up LDKI002 Gerald Wasike 02/04/05 00.00 To come back for reviewvid State with reasons the most suitable data types forthe following fields (8 marks) Patient [No Date registered ‘Amaunt paid vi Which would be the most appropriate primary key feld for tne above table?(1 mark) Jn) What isthe purpose ofa primany key file in database design?(1 mark) KCSE 2008 Paper 1 Qn.18. The information below is maintainad by a gatron of 3 wildlife club in a school. Study and answer me ‘questions mnat follow. wave | Ges | Adwisiontember | Mententiptimber | Gow u ier on aa Serengeti Gime | aw | ate root be cay N08 rox is Gey W782 voor eee srry 3800 001 coo Maio | 26 | 3905 om Soe Jonhine | 2W | 3806 wor pet fe ans 001 oat coy E05 ron ead cern |W | 9 wea i Peul IN 4013 M002, oe 2) Describe the field values, records an file G marks) 1) State the most appropriate primary key for the list. (1 mark) Stace ine most appropriate data cype for tne fields: Admission number (1 mark) |) Membership number. (1 mark 0) fa database was to be created for thelist; Forms, Tables, Queries and Reports are likely to be used. 1) State tne purpose of each of these abjects. (4 marks) 1 Which objects cannot be Used to store the dats in the I'st? (3 marks) 211) Howy many field values are inthe fist?(1 mark) 1] Hew many records are in the list2(1 mark) KCSE 2007 Paper 1 Qn.20. 2) Anead teacner keeps tne following student detals in a database: Name, Address, Town, Date of birth, Marks scored, Fees paid 1) Neme the most appropriate primary key. Give a reason. (2 marks) i For each item in the student's details above, indicate its most appropriate dats type as used in the database (Bmarks| 11) Explain why input screens are better dats entry designs than entering data directly to 8 table (2 marks) 0 Distinguish between: D Atable In word-processing application and 2 table in a database application (2.marks)KCSE 2007 Paper 1 Qn.19. BDA students’ database comprises of students’ details table and fees received table as shown below Students’ details table Surname Midge Name Fiest Name. Admission Numeer Fees recelved table Date Amount Receipt Number Course 1) State the primary key field for each table 1 State tne field which should serve as the linking field for the wo tables. (2 marks) KCSE 2015 Paper 1 (Qh.3. The figure nelow snows ralazonsnip betwaen tables ina database, Table1 Performance ID Table 2 ‘TermCode TemID ‘StdID Year Mathemais ae English ja__ | Kiswahili na Science ‘danafy wo primary and wo foreign keys used in tne relationship. KCSE 2012 Paper 1 Qn.11. Describe the following types of relationsnins as used in Database design: 2)Oneto-one »)One-to-many KCSE 2009 Paper 1 Qn.17. Alecturer keeps tne following student details in a database: name, age, and course. a) Were an expression you would use to compute the year of birt ofa student using this year asthe current year. (2 marks) _) What query expressions would the lecturer use to ist tne students whose age is anove 15 years and below 25 years? (@ marks) ‘Which expression would tne lecturer use to generate: Tne numaer of students In :ne datsoase? 2 marks) \W Tne mean age of te students n tne database? (2 marks)Unit 4. Desktop Publishing (DTP) Introduction and Definition Publishing isthe act of producing publications like newspapers, cards, pamphlets, pictures and calendars of professicnal quality. In the madern eva, the pracess of publishing has changed because of the advent of personal computers and digital princers cnat are able to produce high quality publications. Definition Desktop Definition isthe act of creating visual communications such as brochures, business cards, greeting cards, web pages anc posters among others. Desktop publishing software is application safsware for graphics design. Examples of DTP software include Adobe Pagel/aker, Microsoft Publisher, Quick Xpress, Adobe in Design, Ventura, Serf page plus and Apole page 2 Purpose and Advantages of desktop publishing software Can create complex text anc graphics. |W Allows the user to create various page layouts. ii} Allows the user to print the publications. Advantages of using desktop publishing software + Gives the user power to maripulace tert and graphics and control ofthe page layout + The graphics and text manipulation capabilites of @ DTP sofware far ourweigh those of @ word processor + Items can be edited and formatted independently + Allows te user wien a wide range of templates + Allows 3 common sesting on master pages whicn can than be repeated on omer pages. + Allows production of commercial publications. *Teallows te user to come up wth a wide varery of publications +The Frames do not necessariy have to be arranged in 3 particular order.Designing a publication and Types of desktop publishing softwar Designing a publication Desktop publishing software allows the user to design a variety of publications for print such as business cards, calendars, banners, bilboards, certificates, books, newspapers, among cthers Graphical based: Manipulate graphic objects. Layout based: Assist the user to came up with a varity of layouts Microsoft Publisher window eeMs publisher window has the following features 2) The pasteboard - Areas that hold text and graphies during page layout. ) Toolbox - Set of buttons znat contains various tools that you can use to create and manipulate publications. Rulers - Vertical and horizontal guides. They are used to guide placement of text and graghies to the required postions,

You might also like