Principles of Information Technology
Principles of Information Technology ITEC 110
Popular in Course
Mr. Stuart Kuvalis
verified elite notetaker
Omar Daniel Negrete
verified elite notetaker
verified elite notetaker
SOWK 201 002
verified elite notetaker
BIO 201 - 031
verified elite notetaker
verified elite notetaker
Popular in Management Sciences And Information Technology
This 154 page Class Notes was uploaded by Mr. Stuart Kuvalis on Monday October 19, 2015. The Class Notes belongs to ITEC 110 at Radford University taught by Staff in Fall. Since its upload, it has received 11 views. For similar materials see /class/224668/itec-110-radford-university in Management Sciences And Information Technology at Radford University.
Reviews for Principles of Information Technology
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 10/19/15
Connecting with Computer Science Greg Anderson David Ferm Robert Hilton operating systems Objectives Learn What an operating system is 0 Become familiar with the different types of operating systems 0 Identify the major functions of an operating system 0 Understand how operating systems manage processes Connecting with Computer Science Objectives continued Understand how operating systems manage resources Understand how operating systems provide security Learn how to perform basic operating system le management functions in Windows UNIX and DOS Connecting with Computer Science Why You Need to Know About Operating Systems Operating SystemOS mediates all activity Within computer System knowledge improves ef ciency Practical skills easily acquired Demonstrate basic folder and le functions Connecting with Computer Science What Is an Operating System OS software control center Resident in main memory RAM Interfaces user applications hardware with CPU Supervises and facilitates program execution Connects hardware to CPU with device drivers Common brands Microsoft Windows UNIX Linux Mac OS Connecting with Computer Science Figure 51 An nperating system provides an interface between the user applications and hardware operating system hardware printer I quot 3 hard drive ROM BIOS Connecting with Computer Science What Is an Operating System continued OS has 2 main program regions Kernel system core loaded at boot time by BIOS Modules components provide userdevice interface 39 Platform OS tted to a particular CPU CrossPlatform application runs on multiple platforms Connecting with Computer Science Figure 52 Various components support the operating system kernel I applications operating system 3 user interface tools configuration data registry configuration les hardware I Connecting with Computer Science What Is an Operating System continued Table 51 OS development in historical context Table 5 1 Operating system summary operating approx system date description UNIX 1968 First widely used multiuser multitasking OS Bell ATampT initially for midrange computers CPIM 1975 First OS that allowed business work on personal computers with the associated VisiCalc spreadsheet software MS DOS 1980 First 05 for the IBM PC PC DOS 1981 IBM version of Microsoft MSDOS Mac OS 1984 First widely distributed OS to use a graphical user in terface GUI and a mouse for Apple PCs Mac OS X is closely related to UNIX but runs only on Apple computers Connecting with Computer Science operating system Windows 3x Linux Windows NT Windows 9x Windows 2 000 approx date 1 990 1 990 1 993 1 995 2000 Connectng with Computer Science description Early Microsoft Windows versions including Win dows 31 and Windows 31 1 collectively referred to as Windows 3x provided a graphical interface to what was essentially still DOS quotunder the hood quot An opensource OS meaning that it was developed tested and enhanced by many people as a collabora tive effort Based on UNIX Linux was initially devel oped by Finnish computer science student Linus Tor valds in 1 990 Its development has expanded to include a graphical interface and many other mod ules Those participating in its development make the operating system freely available to everyone al though some companies charge to package or sup port Linux Because of its low cost availability for many types of computers and stability Linux has be come very popular in the last few years Included more advanced security features netvvork support and user administration features than Win dows 3x Windows NT came in two versions Windows NT Workstation for end user computers and Windows NT Server to manage and maintain a network Primarily designed for businesses and tech nical users as opposed to home users The Windows 9x operating systems Windows 95 Windows 98 and Windows Me Millennium Edi tion rely on a DOS core but provide a more user friendly interface and advanced features such as au tomatic recognition and configuration of IIO devices Plug and Play more integrated Internet capabilities and support for hardware devices such as DVDs An upgrade of Windows NT Windows 2000 provides additional features including greater net work support and increased speed and stability There are several versions of Windows 2000 Win dows 2000 Professional is popular for the corporate desktop while Windows 2000 Server Windows 2000 Advanced Server and Windows 2000 Datacenter Server are netvvo rk operating systems con tinued Table 51 continued operating system Windows XP Windows Server 2003 Windows quot Longhorn quot Connecting with Computer Science approx date 2001 2003 2006 exp description Windows XP combined the userfriendly features of Windows 98 and Windows Me with the strengths of Windows 2000 Windows XP provides an upgraded user interface support for multiple users better per formance and more support for multimedia such as audio and vide0Windows XP is available in three versions Home Edition Professional and Tablet PC Edition Windows Sewer 2003 is a network operating system with additional features for managing and maintaining a network It provides increased security enhanced file and print server support support for remote access and moreWindows Server 2003 comes in several versions including Standard Edition Enterprise Edition Datacenter Edition Web Edition and Small Business Server 2003 The newest version of Windows is currently code named quotLonghornquot This version of Windows is a rad ical departure from the prior architectures especially in the area of the file management system Longhorn has a taskbased user interface and new security en hancements Types of Operating Systems OS classi cation schemes based on features and use Singletasking eg DOS and Windows 3x Multitasking eg Windows Mac OS UNIX Network Operating Systems NOS eg NetWare UNIX Windows 2000 Server and Windows Server 2003 Connecting with Computer Science Types of Operating Systems continued OS design parameters tailored to customer base Microsoft Windows and Mac OS appeal to home and small business users Userfriendly interfaces and multimedia capabilities UNIX often the OS of choice in the server environment Stability multitasking security multiprocessing 0 Every multipurpose device with CPU must have an OS Desktops clients servers PDAs cell phones appliances Connecting with Computer Science Functions of an Operating System All operating systems perform four basic functions Provide a user interface Manage processes Manage resources Provide security Connecting with Computer Science Provide A User Interface User interface program provides system access 0 Two interface types Command line interface console operating system Text input entered at command prompt Output displayed as characters and numbers Graphical User Interface GUI Input devices keyboard mouse touch screen audio Command OS via menu selections in open window Connecting with Computer Science Figure 53 The Windows command prompt emulates a DOS environment top the default GUI interface of Windows XP bottom A21 ping icrosofl Hindu M3 Version 51A26331 it Copyright 19352861 Microsoft Corr command Documents and Seliingsrbhillongtcd c entered gtping 19245391 inuino 192168 1 wilh 32 bytes of dale Eplv from 192168 1 bytes32 iie ZUS d 392in rom 93911ng n 6 ep 9 rom y es 1 IS Commaquot an r on 192163 bytes32 tine1ns 111 executed ing statistics for 192168E1 Packets Serll 5 39 gt ecelved ALosl B 33 loss Dnroaumate is Miniu 15 Nani nu 2ms Huer command prompt gt age Ills window desktop Connecting with Computer Science Provide A User Interface continued 0 Example interfaces GUI Microsoft Windows and Mac OS Commandline console Window Standard UNIX can be tted with GUI DOS disk operating system MS console OS Windows backwardly compatible with DOS Connecting with Computer Science Manage Processes The OS loads starts supervises stops processes Process is a running program Processes may start spawn other processes to support them In Windows Task Manager shows running processes Accessed by pressing CtrlAltDel keys Not available for singletasking DOS Connecting with Computer Science Figure 54 Windows XP Task Manager Windows Task Manager Eile gm ew Shut Down lien Applicatims Performance p Netwwlm 39 g Users A ardexe rbhllton DB 1720 K hph39pm09exe SVSTEM DD 632 K HOTS I39MJEXE rlbhilton CID 1484 K sqll39nengrexe rbhilton 00 1944 K Dllcmd exe rlbh ton CID 1403 K HntTrayexe rhhi ton OD 932 K ctfmanexe rbh ton DB 2312 K reelschedexe rhhilton OD 156 K hphmm03exe mbhilton 00 1800 K mmsgr xe bhiltcn OD 532 K McVSEmmexe rlhh ton DD 6476 K Cbeltexe rhhilton 00 3092 K arhra39xx n rlhhlirnn nn 1 Q7n K v 1 haw processes from all users Processes 53 CPU Usage 3 Commit Charge 351392395391l 1250M Connecting with Computer Science Figure 55 Viewing preeesses in UNIX using the p5 command memand Prompt a telnet Rememberde 7 7 E Di zgcarusue5ereau p M PID II IIHE CHD 23612 ptsS 830 hash 23638 pteE 98 new 2363 ptsS 659 311 23622 ptsS ma nan iuarus wehar edw Connecting with Computer Science Manage Processes continued 0 In UNIXLinux running process information also available type psauX at console prompt for running processes CPUs only run one process at a time Von Neumann machine supports serial execution Only one instruction from one single program per clock cycle Connecting with Computer Science Manage Processes continued 0 Time slicing OS time management method Allocate multiple processes to a single CPU Illusion of simultaneous execution Attributable to the different speeds of devices CPU executes billions of instructions per second Memory keyboard monitor and network adapter slower by many orders of magnitude CPU multitasks while waiting Connecting with Computer Science Manage Processes continued 0 Ef ciencies generated with cost of system complexity OS needs to distinguish between readywaiting process OS needs to service asynchronous IO device request Interrupt handling routine reallocates processes to CPU Connecting with Computer Science Manage Resources OS is a resource manager Con gure IO devices Within environment Plug and Play PnP automates process from 1995 Relieve applications of direct IO interface Recognize and eliminate deadlock Circular wait for resources freezes system OS must remedy or system will need rebooting Connecting with Computer Science Manage Memory The OS is a resource manager Monitor free space in memory Load programs and data into memory location Keep track of instruction trace Reallocate memory as processes come and go Connecting with Computer Science Provide Security The OS protects memory and other resources Unintended Violation Application writes into address space of another process OS prevention place boundaries around processes 0 Intended Violation security issue Unauthorized access of programs or devices undesirable OS prevention System administrators set up password protected accounts Group policies automatically assigns rightsprivileges Connecting with Computer Science Using An Operating System OS concepts enable adaptation to individual design 0 Practical knowledge also essential Starting and running programs Managing system resources Skills focus basic le management in various operating systems Connecting with Computer Science Managing Disk Files 0 Basic OS le management features Dynamic le naming Folder or directory creation and modi cation Formal folder structure needed System is treelike Single root level and one or more branches Files Viewed as leaves or nodes on the tree structure Windows Explorer manage les through graphical interface Connecting with Computer Science Figure 56 Windows Explorer graphical interface for manipulating files 3 C 39iUML Ede Edit sew Figantes Iools Help em a a aw lama mm x Nam a in Type Date Mod ed E A I UM Commvs 1 ice Manson Masterad ansrzum 855 PM 5 a madam Q LML Deplmwuerhss 57 KB Mcrosuft Visit Stand 2115mm 555 PM g g mg a UNIL Static Slmctureyss 162 KB Microth Wm Stern 2115mm 855 PM m E VBWatk I um use Casmss 4 KB Mcrosoft VisioStenc 2115mm 355 pm a V5 a mm 3 werddocs a Work a mrluava 3 amp 3 9 Mul mdiaFlles 01 3 2 DVDRWDrivelEt a J CDRW Drive F E v Removable Disk a 3 Br CommlPand E a Shared Documents 3 h lu39ds s ligaments a Renee39s Documents a r ltpnlaplop39s Downens A g Spmce s Doorman E MyNetworkPlates g Recycle Bi I 1 BENZ a Connecting with Computer Science Managing Disk Files continued UNIXDOS command prompt access directory through the console Window 0 UNIXDOS commandline switches parameters ags Example DOS command DIR Cquotlt P Causes OS to list all les located at the root of the C drive P switch modi es DIR command with screen pause In DOS enter HELP ltcommandgt to get more details In UNIX or Linux enter man ltcommandgt for manual Connecting with Computer Science Figure 57 UNIX commandline interface for displaying fi es and folders I Select ammand Prompt telnet lCdrUSMEhELGdU I S ppstepsib c c3453htn mynose Cdnt cs4l htn new 39RCppt A deadlatter newu asignDucwnantDOC dunnyn mew1asstxt ail firs newfile n0ut firstc nldhist bin firsmhi powerpnintr39 testes flnater shellshtn J construcigif f1natarc annuic n1323htm gregajpg syllabuscs113html e31136htn hold test cleZBhtm 1101M testth 32550htm indexhtml teatest c32750htm mail as35htm icarusuaharedu i Connecting with Computer Science Figure 5 3 DDS commandline interface showing the results of the DIR command 2quot Luulmund Prompt UMLgtdir H In Volume in drive C has no label Volume Serial Number is FQCGH8 Directory of CUML 12182833 z PH DIR 210f2903 0483 PH DIR l Filels 3 amp36 bytes 2 Dirlsl 216127k8330 bytes free CUHLgti 215I2001 6855 PH 41l2 UHL Conponenlvss 2l1512331 3855 PH 68638 UHL Deploymentuss 2152051 0855 PH 185368 UHL Static Structurevss 21152 01 8855 PH 7616 UHL Use Casevss Connecting with Computer Science Managing Disk Files continued Present 8 le and folder operations in XP DOS UNIX Partitioning disks dividing surface into speci c areas WindowsDOS use FDISK to partition drives UNIX use fdisk Formatting disks Arrange disk surface into addressable areas Set up basic directory tree structure Copy OS onto backup boot disk Connecting with Computer Science Table 52 Formatting disks in various operating systems operating system commandsprocedures to format disks Windows Nearly all types of disks oppy disks hard disks removable disks can be formatted in Windows through the Windows Explorer appli cation Some specialized removable disks have their own format program but most Can be formatted directly through the operating system format program The disk can be formatted in Windows by rightclicking on the drive that needs to be formatted and selecting Format from the drop down menu In the Format window various options can be set Most of them are defaults and should not be changed You can do either a full format or a quick format The full format marks the tracks and sectors on the disk and puts the basic tree structure on the disk The quick for matjust puts an empty tree structure on the disk There is also an option to put the system startup les on the disk Once the options have been selected and the Start button has been clicked the disk is formatted The OS informs you when the format is complete and the disk is ready to use con tinued Connecting with Computer Science Table 52 continued operating system commandsprocedu res to format disks DOS Disks are formatted in DOS via the FORMAT command at the com mand line The command can have one of the following forms FORMAT volume F82file system V1abel Q Azsize C X FORMAT volume V label Q Fzsize FORMAT volume V label Q T tracks N sectors FORMAT volume V label 9 FORMAT volume Q Following the FORMAT command and the volume drive are vari ous switches that give additional direction to the format process You can type HELP FORMAT at the DOS prompt to get additional help on the FORMAT command UNIX In UNIX you can use the fdformat command to format a disk The command takes the form fdformat dDeEleL1nMqux b label B filename t dostme devname The items in the brackets are optional switches that modify the command You can get more detail on the zfarmat command by entering man fdformat at the UNIX console prompt UNIX also has the format command to format hard disks Enter man formarat the UNIX prompt for more help on formatting hard drives Connecting with Computer Science Managing Disk Files continued Creating folders subdirectories Treelike le structure available after partitioning and formatting Main level called the root Create one or more folders at root Each folder lies Within root folder or Within another folder level Connecting with Computer Science Table 53 Creating folders operating system Windows commandsprocedures to create folders Folders can be created in Windows in two ways 0 Right click the blank area of the far right pane of Windows Explorer when you have already selected a drive or folder After right clicking select New from the drop down menu and then se Iect Folder A folder called New Folder is now appended to list of files andor folders in the right pane You can then click that folder and rename it to whatever name you like Select the folder you want to be parent to the new folder and then select File New Folder from the top menu of Windows Explorer ln DOS the command to make a folder is MKDIR or MD which stands for quot make subdirectoryquot The syntax for theMD command is MD dri Ve pa th In this syntax the drive is optional and path refers to either a single folder name or an entire parent child Structure In a command line interface such as DOS you need to be aware of the concept of current directory Since there isn t a graphical representation of the parent child structure the user has to be able to navigate the structure by typing commands instead of clicking on a particular folder The DOS command to move to a particular level is CHDIR or CD The path in both the MD and CD syntax is the folder structure If the command MD F1 F2 F3 were typed at the DOS prompt a folder structure would be created with the folder named F1 created as a child to the root F2 would be created as a child to F1 and F3 as a child to F2 You could then move the current directory or folder to F2 by enter ing CD F1 F2 quot If your current folder were the root folder you would get an error if you tried to make F2 your current folder by en tering quot CD F2quot because F2 is not a child of the root If you just enter a folder name with the MD command such as quotMD F4 quot the F4 folder would be created as a child to your current folder Therefore when using the MD command you need to either use the entire tree structure or use the CD command to move the current folder to the parent and then use the MD command with the name of the folder you want to create at that level Connecting with Computer Science operating system commandsprocedures to create folders UNIX UNIX and DOS are very similar in many respects The UNIX iiiairy command works like the DOS MKDIRIMD command UNIX also uses medirto create subdirectories folders In addition the current subdirectory concept holds true in UNIX You can change your current subdirectory by using the cai command which is the same asthe CD command in DOS Connecting with Computer Science Managing Disk Files continued Folders in another folder are child folders or subdirectories Container folders are parents Structure has unlimited depth 5 10 recommended Each OS enables user to create directories folders Folders named according to speci c rules UNIX is case sensitive DOS and Windows are not Connecting with Computer Science Managing Disk Files continued 0 Listing the Contents of Drives and Folders XP lists drivefolder contents Via GUI Windows Explorer DOSUNIX pass same information with textbased commands Connecting with Computer Science Table 54 Listing the contents of drives and folders operating system commandsprocedures to list contents of folders or drives Windows The main program for viewing drives folders and files in Windows is Windows Explorer It can be executed by clicking Start All Pro grams Accessories Windows Explorer On your system Windows Explorer also might be on the desktop as an icon You can also start Windows Explorer by holding down the Windows flag key and pressing the letter e In Windows Explorer you can browse the tree structures of your disk drives If you click on the plus symbol next to a folder it opens up the contents of the folder In general folders are on the left and files on the right although you can also see folders on the right at times If you rightclick a file or folder and select Properties from the drop down menu you can view the details about the object that you39ve selected DOS The DOS command to list the files of a drive or folder is DIRThe syntax is DIR drivez Path filename A 1actributes B c D L N O 150rtorder11 P Q S T t timefield11 W le 4 As you can see there are many switches that can be used to modify the way the DIR command displays the output You specify the drive letter folders path and le specification of the drive or folder you want to list continued Connecting with Computer Science Table 54 continued operating system commandsprocedures to list contents of folders or drives UNIX The UNIX command to list files and folders is is for quotlistquot and it op erates in a similar manner to the DOS DIR command The syntax is 18 la lAl lbl 6 Hi 61 H H 9 H 1 H lm 0 10 Ht H Hi 5 H 11 Ht lpathnames Like its DOS counterpart the is command has many switches to mod ify the manner in which files are displayed You can nd information about the switches by entering man is at the command prompt Connecting with Computer Science Managing Disk Files continued Renaming Folders and Files Every OS provides procedures for renaming Connecting with Computer Science Table 55 Renaming folders and files operating system Windows DOS UNIX commandslprocedures to rename folders or files Folders and les are renamed in Windows Explorer by right clicking the folder or file and then selecting Rename from the dropdown menu The cursor moves to the highlighted folder or file name and you can type in the new name You can also re name a folder in Windows by clicking a folder to select it and then pressing F2 and entering the new name The command syntax for renaming folders in DOS is the same for renaming files RENAME drive path oldfilename newfilename The full path is required for the path folder structure and name but only the new name is required without repeating the path The UNIX syntax for renaming a le or directory is the same as for moving it mv di rec I cry nemame Where directory is the full path specification to the subdirectory or file and newname is the desired new name Connecting with Computer Science Managing Disk Files continued Deleting Folders and Files Every OS allows for les to be deleted XP also allows recovery Recycle Bin Wildcard symbol used to select directories Asterisk and question mark C are wildcards Example exe signi es all exe les Connecting with Computer Science Table 5 6 Deleting folders and files operating system commandslprocedures to delete folders and files Windows In Windows there are two ways to delete a folder 0 Rightclick the folder you want to delete then select Delete from the dropdown menu I leftclick the desired folder and then press Delete In both cases you see a message asking if you39re sure you want to delete you must click Yes or press Enter to confirm Note that in Windows deleting a folder also deletes all les and folders contained in the deleted folder continued Connecting with Computer Science Table 56 continued operating system commandsprocedures to delete folders and files DOS DOS has different commands for deleting folders and files The DOS command to delete a folder is RD S Q drivepath where path is the folder tree with the folder you want to delete at the end The folder has to be empty before it can be deleted in DOS unless the IS switch is specified The DEL command is used to delete les in DOS DEL PJ F S Q Aattributes filenames You can use wildcards for a single character or for any character in the filenames speci cation to delete more than one file at a time and as you can see there are many switches that modify the way that the DEL command executes The DEL command doesn39t send files to a holding area like the Recycle Bin in Windows When you issue the DEL command the file is marked as deleted and only a special recovery pro gram can get it back The DEL command doesn39t actually delete the data in the file It remains on the disk until some other file uses that space UNIX In UNIX the rm command is used to delete folders subdirec tories or files For subdirectories the r switch has to be used to indicate that a subdirectory rather than a file is being deleted rm filename ii The rm command deletes the file or files specified by filename The optional switch r causes the rtn command to prompt you before each le is deleted You can use the UNIX wildcards to specify groups of files to be deleted More information on the rm command can be found by enter ing man rm at the command prompt Connecting with Computer Science Managing Disk Files continued Copying Files and Folders Files can be copied into folders or stored at root Possible after partitioningformatting le creation Connecting with Computer Science Table 57 Copying files and folders operating system commandsfprocedures to copy folders and files Windows A5 with other functions there is more than one way to copy files from one location to another Windows Explorer is once again the graphical tool used to perform the file copying The easiest safest and most consistent way to copy files is by using the copy and paste functions Simply rightclick a file and select Copy from the drop down menu Next rightclick on the folder or drive you want to copy the file into and click Paste A copy of the le is placed at the selected location You can also copy files by holding down Ctrl while left clicking the file and while holding the left mouse button down dragging the le to the desired folder andor drive This process is risky in that if you accidentally let go of the key or button before you get to your desti nation you may end up with the le in a location that you didn t plan and you might also delete the file at its original destination Folders can be copied in the same manner as les Any files or child folders in the copied folder are also copied Note that multiple files andor folders can be selected for copying by using the left mouse button and either Shift or Ctrl Shift is used to select contiguous files holding Shift and clicking the first and then the last file in a series selects the whole group If Ctrl is held down each click on a file or folder adds it to the selection can rinued Connecting with Computer Science Table 5 7 continued operating system commandsprocedures to copy folders and files DOS As with Windows DOS also has more than one way to copy files They are similar but one copies child folders and the other does not The COPY command can be use to copy either individual les or groups of files The syntax for the COPY command is COPY ED V N Y YI Z A I B source A I B 4 source A 1 ms destination A B There are a lot of optional switches that can be used with the COPY command but normally the COPY command is used by specifying just the source and destination separated by a space Because a space separates the source and destination the file or folder paths must be enclosed in quotes if there are any spaces in them For example COPY c windowswin ini quot c program fi les conunonwin ini quot For help with the additional capabilities of the COPY command enter HELP COPY at the DOS prompt Another copying command available in DOS is the XCOPY com mand The syntax for XCOPY is very similar to the COPY com mand except that it has considerably more switches XCOPY source destination E A I M Ddate P 8 E V W C I Q F L G H E R T UI K N 0 EX Y Y Z I EXCLUDE filel file2 file3 The main difference between the two commands is that XCOPY can copy folders and subfolders in addition to copying files al though the XCOPY command has many extended capabilities such as being able to copy files within a specific date range Connecting with Computer Science operating system commandsprocedures to copy folders and files UNIX The cp command is used in UNIX to copy les The syntax for cp is Up filefrcun fileto lefrom and fileto are full path specifications to the quotfrom filequot location and the quotto filequot location UNIX has a greater set of wildcards but still uses the asterisk and question mark in the same manner as in DOS The use of the asterisk wildcard followinga lefmm or leto specification in structs the cp command to include all of the files and subdirecto ries contained in the main subdirectory specification Connecting with Computer Science Managing Disk Files continued Moving Files and Folders Similar to copying les Copy command followed by delete command Connecting with Computer Science Table 58 Moving files and folders operating system commandsprocedures to move folders and files Windows Moving files in Windows follows nearly the same process as copy ing them The safest way is to rightclick the folder or file you de sire to move then select Cut from the drop down menu Next go to the location that you want to move the folder or file into right click and select Paste from the dropdown menu You can also move a file by clicking it and then while holding down the left mouse button dragging the selected file or folder to the folder or drive you want to move it to and releasing the mouse button This is called quotdrag and drop quot and is not as reliable a method as cutting and pasting DOS The MOVE command is used in DOS to move files The syntax for MOVE is MOVE Y Y drive path filenamel destination MOVE works like COPY except that it deletes the original file after it is copied to the destination Note that the MOVE command doesn39t work with folders There39s no single command in DOS to move a folder however folders can be copied and then deleted from the original location using the commands described in Table 56 continued Connecting with Computer Science Table 58 continued operating system commandsprocedures to move folders and files UNIX In UNIX the mv command is used to move subdirectories as well as files The syntax is not oldname newname r The r switch is used to include all child subdirectories and files in the move process Connecting with Computer Science One Last Thought OS knowledge essential for computer scientists OS concepts taught alongside practical skills Expand OS toolkit beyond basic le management Advanced study recommended Connecting with Computer Science Summary OS is the software control center of the computer OS consists of a kernel and other system programs OS loaded into RAM by program in BIOS chip OS may be single tasking or multitasking Time slicing OS method for multitasking Connecting with Computer Science Summary continued Basic OS tasks provide user interface manage processes manage resources provide security Two OS interfaces GUI and console window OS supervises program in execution process OS interfaces hardware elements through drivers OS protects system from intendedunintended violations Connecting with Computer Science Connecting with Computer Science Greg Andersen David Freer Robert Hilton viii numbering systems and data representatinn Objectives 0 Learn why numbering systems are important to understand 0 Refresh your knowledge of powers of numbers Learn how numbering systems are used to count Understand the signi cance of positional value in a numbering system 0 Learn the differences and similarities between numbering system bases Connecting with Computer Science Objectives continued 0 Learn how to convert numbers between bases Learn how to do binary and hexadecimal math Learn how data is represented as binary in the computer Learn how images and sounds are stored in the computer Connecting with Computer Science Why You Need to Know About Numbering Systems Computers store programs and data in binary code Understanding of binary code is key to machine Binary number system is point of departure Hexadecimal number system Provides convenient representation Written into error messages Connecting with Computer Science Powers of Numbers A Refresher Raising a number to a positive power exponent Selfmultiply the number by the specified power Example 23 2 2 2 8 asterisk multiplication Special cases 0 and l as powers Any number raised to O 1 eg 105550 1 Any number raised to l itself eg 105551 10555 Connecting with Computer Science Powers of Numbers A Refresher continued 0 Raising a number to a negative power Follow same steps for positive power Divide result into 1 eg 2393 1 23 125 Connecting with Computer Science Counting Things Numbers are used to count things Base 10 decimal most familiar The computer uses base 2 called binary Base 2 has two unique digits 0 and 1 Connecting with Computer Science Counting Things continued Hexadecimal system used to represent binary digits Base 16 has sixteen unique digits 0 9 A F Counting for all number systems similar Count digits de ned in number system until exhausted Place zero in ones column Carry one to the left Connecting with Computer Science Positional Value Weight assigned digit based on position in number Determine positional value of each digit by raising 10 to position Within number Determine digit s contribution to overall number by multiplying digit by positional value Consider 5 in 3456123 radix 10 decimal point Positional value 101 Overall contribution 5 X 101 50 Connecting with Computer Science Figure 41 Positional 1values fur a base 10 number 10 1 1021 10 10 2 2 10 01 103100 I 10393 00 10 1000 1 3456123 Connecting with Computer Science Positional Value continued Number sum of products of each digit and positional value Example 3456123 3 X 103 4 X 102 5 X 101 6 X 1001X10391 2 X 10392 3 X 10393 Numbers in all bases can be de ned by position Base 2 Multiply each digit by 2 digit position Base 16 Multiply each digit by 16 digitposi on Base b Multiply each digit by b digit Position Connecting with Computer Science Figure 42 Positional values for a base 2 number 2 1 2 2 2 2 2 25 224 238 l z393125 1011011 Connecting with Computer Science How Many Things Does A Number Represent Number sum of each digit X positional value Translate number of things to accord with base 10 eg 10012 is equivalent to nine things 1 20 O 21gtlt0 22lt1 23 General procedure for evaluating numbers any base 1 Calculate the value for each position of the number by raising the base value to the power of the position 2 Multiply positional value by digit in that position 3 Add each of the calculated values together Connecting with Computer Science Converting Numbers Between Bases Any quantity can be represented by some number in any base Counting process similar for all bases 1 Count until highest digit for base reached 2 Add 1 to next higher position to left 3 Return 0 to current position Conversion is a map from one base to another Identities can be easily calculated Identities may also be obtained by table lookup Connecting with Computer Science Table 41 Cnunting in cliFfererrt bases base 1D base2 base 113 II CI 0 1 1 1 2 1C 2 3 11 3 4 11m 1 5 1111 5 f5 11 E T 111 1 8 1000 8 393 1IJCI1 El 10 mm A 11 1011 B 12 1101 C 13 1101 B 11 1110 E 15 1111 F 15 1111000 1o 11 1131101 11 15 10010 12 1E 1HJ I1 13 21 101cm 14 Connectng with Computer Science Converting To Base 10 0 Three methods 1 Table lookup more extensive than Table 41 2 Calculator 3 Algorithm for evaluating number in any base 0 Example consider 169AE in base 16 Identify base 16 Map positions to digits i g 1 Q Raise multiply and add 169AE 1 X 164 6 X 163 9 X 162 10 X 161 14 X 160 92590 Connecting with Computer Science Converting From Base 10 0 Three methods 1 Table lookup more extensive than Table 41 2 Calculator Connecting with Computer Science Converting From Base 10 continued 3 Algorithm for converting from base 10 1 Divide the decimal number by the number of the target base for example 2 or 16 2 Write down the remainder 3 Divide the quotient of the prior division by the base aga1n 4 Write the remainder to the left of the last remainder written 5 Repeat Steps 3 and 4 until the Whole number result is O Connecting with Computer Science Converting From Base 10 continued Practice conversion algorithm nd hexadecimal equivalent of decimal 45 Divide 45 by 16 base Write down remainder D Divide 2 by 16 Write down remainder 2 to the left of D 2D Stop since reduced quotient 0 Check 2D 2 X 161 13 X 160 32 13 45 Connecting with Computer Science Binary And Hexadecimal Math Procedure for adding numbers similar in all bases Difference lies in carry process Value of carry value of base Example 1011 w 11000 Carry value for above 102 1 X 101 0 X 100 210 Procedure for subtraction multiplication and division also similar Connecting with Computer Science Figure 44 Subtraction using base 2 numbers 0islessthan1so10 101 is borrowed from the next column to the left reducing it to 0 10 39 1 1 0 101 1 After the borrow 11 0 thetop value isO 000 Connecting with Computer Science Data Representation In Binary Binary values map to twostate transistors 0 Bit fundamental logicalphysical unit 10 onoff 0 Byte grouping of eight bits nibble 12 byte Word collection of bytes 4 bytes is typical Hexadecimal used as binary shorthand Relate each hexadecimal digit to 4bit binary pattern Example 1111101011001110 F A C E see Table 41 Connecting with Computer Science Representing Whole Numbers Whole numbers stored in xed number of bits 200410 stored as 16bit integer 0000011111010100 Signed numbers stored with two s complement Left most bit reserved for sign 1 neg and 0 pos If positive store with leading zeroes to t eld If negative perform two s complement Reverse bit pattern Add 1 to number using binary addition Connecting with Computer Science Figure 45 Storing numbers 111 a two39s complement 8bit field 52 52 decimal is equivalent to binary 110100 00110100 In two39s complement positive numbers are simply stored as binary values with leading zeros to fit the field size 52 52 decimal is equivalent to binary 110100 1101100 Start with 00110100 Flip the bits 11001011 Add 110 get 11001100 Connecting with Computer Science Representing Fractional Numbers Computers store fractional numbers neg and pos Storage technique based on oatingpoint notation Example of oating point number 1345 E5 1345 mantissa E exponent 5 moves decimal IEEE754 speci cation uses binary mantissas and exponents Implementation details part of advanced study Connecting with Computer Science Representing Characters Computers store characters according to standards ASCII Represents characters with 7bit pattern Provides for upper and lowercase English letters numeric characters punctuation special characters Accommodates 128 27 different characters Globalization places upward pressure Extended ASCII allows 8bit patterns 256 total Unicode de ned for 16 bit patterns 34168 total Connecting with Computer Science Representing Images Screen image made up of small dots of colored light Dot called pixel picture element smallest unit Resolution pixels in each row and column Each pixel is stored in the computer as a binary pattern 0 RGB encoding Red blue and green assigned to eight of 24 bits White represented with IS black with Os Color is the amount of red green and blue speci ed in each of the 8bit sections Connecting with Computer Science Representing Images continued Images such as photos stored with pixelbased technologies 0 Large image les can be compressed JPG GIF formats Moving images can also be compressed MPEG MOV WMV Connecting with Computer Science Representing Sounds Sound represented as waveform with Amplitude volume and Frequency pitch Computer samples sounds at fixed intervals Samples given a binary value according to amplitude bits in each sample determines amplitude range For CDquality audio Sound must be sampled over 44000 times a second Samples must allow gt 65000 different amplitudes Connecting with Computer Science Figure 46 Digital sampling of a sound wave zquot Amplitute Time Connecting with Computer Science One Last Thought 0 Binary code is the language of the machine Knowledge of base 2 and base 16 prerequisite to knowledge of machine language Computer scientists are more effective with binary and hexadecimal concepts Connecting with Computer Science Summary Knowledge of alternative number systems essential 0 Machine language based on binary system Hexadecimal used to represent binary numbers Power rule for numbers de nes selfmultiplication 0 Any number can be represented in any base Connecting with Computer Science Summary continued Positional value weight based on digit position Counting processes similar for all bases Conversion between bases is onetoone mapping Arithmetic de ned for all bases Data representation bits nibbles bytes words Connecting with Computer Science Connecting with Computer Science Greg Andersen David Freer Robert Hilton charmquot database fundamentals Objectives Consider the Widespread use of databases Take a brief tour of database development history Learn basic database concepts 0 Be introduced to popular database management software See how normalization makes your data more organized Connecting with Computer Science Objectives continued 0 Explore the database design process Understand data relationships Gain an understanding of Structured Query Language SQL Learn some common SQL commands Connecting with Computer Science Why You Need to Know About Databases Data must be organized for consumption Effective computer scientists know database design Normalization multistep database design process Structured Query Language SQL interface for storing modifying retrieving data Connecting with Computer Science Database Applications Database Data structure built out of logical relations Affords data manipulations through queries Database applications are pervasive Range from human genome to space shuttle missions Databases important for both living daily life and doing computer science Connecting with Computer Science Brief History of Database Management Systems 0 1970 1975 Work of IBM employees EF Codd and C Date Create theoretical model for database structures Model has become foundation for database design Software for organizing and sorting data System R by IBM and Ingres by UCBerkeley Deploy Structured Query Language SQL SQL has become database standard Database management system DBMS for PCs Connecting with Computer Science Brief History of Database Management Systems continued 1970 1975 continued Wayne Ratliff of MartinMarietta develops Vulcan 1980 present Vulcan renamed dBASE 11 there is no dBase I Popularity of dBASE II inspires other companies Paradox Microsoft Access or FoxPro Databases become essential for business Corporate decision making Systems inventory management to customer support Connecting with Computer Science Database Management System Fundamentals 0 Six main functions of a DBMS Manage database security Manage access of multiple users to the database Manage database backup and recovery Ensure data integrity Provide an enduser interface with the database Provide or interface with a query language to extract information from the database Connecting with Computer Science Database Concepts Basic elements of a database Database collection of one or more tables entities Table divided into rows and columns spreadsheet Row record or tuple collection of columns Column eld or attribute Represents speci c information Set of possible column values is called domain Index order facilitates information access Connecting with Computer Science Figure 31 A database table consists of rows and columns iii Bongoorig able SonLNm Mitch Mburn urrn TmlrNum Mbum amn MILch 1 oment an Soldier TM 911 3 Shockn Y39all Toby Keith MP3 CW Beer For My Horses TK 626 6 Unleashed Toby Keith MP3 CW Big Time PG iii 14 Shaking the Tree Peter Gabriel CD POP Come As You Are NIRV 24 3 Neverrnind Nirvana MP3 NW Lithium NIRV 24 5 Neverminri Nirvana MP3 NW One Tree Hill U2 14E 9 The Joshua Tree U2 CD NW Oriya Lad DB 78 18 Best 0 Bo mgo Oinch Boingo CD NW Sledgehammer PG 1N SShaMngtheTree Peter Gabriel CD POP Sloop JohnB BB 45 7 Pet Sounds The Beach Boys Mimi CLA The Thrill is Gone BEN 93 B The Best of BB King BB King MFS ELU With or Wr rout You U2 145 3 The Joshua Tree U2 CD NW 9 N ti Ferraro Mgr r gag a 11 Connecting with Computer Science Indexes Index data structure that organizes records according to speci c columns Examples music database and telephone book Chief advantages Flexibility many different columns to sort against Searching and retrieval are sped up 0 Chief disadvantages Extra storage space Updating takes longer Connecting with Computer Science Indexes continued 0 An example of indexing grocery store shopping Figure 82 Your use database concepts in your everyday life elem UPC BrandName Amount 1 Description Price L 020190001029 Beefies 1 Frozen Pizza 325 993059377379 Pure Juice 9 Elech ify Soda 229 69499522313 Popper 6 Microwave Popcorn 259 36949826356 SugarD 12 Sweet Cakes 379 9918746255671 Eleefies 2 Chimichanga 079 h 233254513398 CooIQ 1 Root Beer Fee Cream 4225 574027764671 CheesePleeze B Macaroni and Cheese 049 995193349511 CoolQ 1 Cookie Dough Ice Cream 4125 372993719232 Popper 3 Grandma Goose Popcorn 139 6 0 000 Renew 114quot 1 DIHIHllofg Connecting with Computer Science Indexes continued 0 Information in a database kept in sequential order Key columns used to determine sort order Sort grocery items by UPC column as key Sort grocery items by BrandName and Description Media used to manipulate or View data Reports forms labels lowlevel le IO source code Connecting with Computer Science Figure 83 Database records snrted using the UPC column as a key n a UPS BramLNamo Amount Dmrlptlon Price 1 828188881828 Eee es 1 Frozen Pizza 8325 233284818888 138le 1 Hunt Beer Ice Cream 428 372883718232 Pauper 3 Grandma Guuse Pupcum 8178 574827784871 ChaesaPleeza 8 Macaroni and Cheese 848 788488522313 Popper 8 Micruwmre Pupcurn 288 888488283587 SugarD 12 Sweet Cakes 378 888748255871 8321135 2 Chimichanga 878 888183848511 CUUIQ 1 Cuukia Dough Ice Cream 425 883888377373 Pure Juice 8Electrily Soda 228 8 888 Ramm l l l bb1nluf Connecting with Computer Science Figure 04 Database records sorted by BrandName anti Descrirarion 170101111 by Brand Dcocziobir UPC BramLNamo Amount 1 Doso p on Prloo r 000740255071 Bee es 213himiciranga 070 020100001020 Beefies 1 Frozen Pizza 325 574027704071 CheeeePIeeze 0 Macaroni and Cheese 040 005103040511 CoolQ 1 Cookie 13oth Ice Cream 425 233254510000 CoolQ 1 Root Beer ice Cream 425 372003710232 Popper 3 Brandma Goose Popcorn 170 700400522313 Popper 0 Microwave Popcorn 2 50 003050377373 Pore Juice 0 Elecoify Soda 2120 000400203507 SogarD 12 Sweet Cakes 370 111 0 000 mo 1 LIAM of o Connecting with Computer Science Normalization Normalization Standard set of rules for database design Process sequence of stages called normal forms There are five normal forms Third normal form provides suf cient structure Three database design problems solved Representation of certain realworld items Redundancies repetitions in data Excluded and inconsistent information Connecting with Computer Science Preparing For Normalization Gathering Columns Make a list of all pertinent elds columns or attributes Source of elds end user reports eg Song inventory Write elds on your column list Review the input forms that the user has speci ed Each eld from report converted to column in table Connecting with Computer Science Figure 85 Enduser report with table columns highlighted Songs ArtistNme SangNmne mman AlbwnNmre Meaiafjpe GZWGJE I BB King The TH is Gone 8 Tl e Best o39lBB Kirg MP3 1U Nirvana Come AsYou Are 3 Neverrnind MP3 MN Nirvana Lilhiun 5 Nevermind MP3 MN 05190 Boingo Oriya Lad 16 BestOBoinzo CD MN Peter Gabriel Big Time 14 Shaking the Tree CD POP Peter Gabriel Sledgehammer 3 Shaking the Tree CD POP The Beach Boys Sloop John B 7 Pet Sounds Viwl CLA Toby Keith American Solder 3 Shock39n Y39all MP3 CW Toby Keith Beer For My Pluses 5 Unleashed MP3 CW U2 One Tree Hil 9 The Joshua Tree CD MN U2 With or erham Ycu 3 Th Joshua Tree CD MN Connecting with Computer Science Preparing For Normalization Gathering Columns continued 0 Reconcile elds in report to column list Create tables of columns Combine associated elds Logically group related information Example Information on artist and song les Gather data to create physical music database Connecting with Computer Science Figure 816 Additimal table columns can be gleaned from input forms 2 EH Song ame ISledgehammer TradltNum 3 AlburILN arm IShaking the Tree Medianpa ICD um ArlisLName lF39eteI Gabriel Exeme tade IPUP Record b In Ivar of 11 Connecting with Computer Science First Normal Form Unnormalized table rowcolumn intersection with two or more values 0 First normal form INF eliminates redundancies Create a new record for the duplicated column Fill in blanks so all columns in record have a value Columns with duplications the AlbumNum AlbumName ArtistCode ArtistName MediaType and GenreCode Remaining redundancies addressed later Connecting with Computer Science Second Normal Form Next steps Assign a primary key to the table Identify functional dependencies Within the table Primary key PK a column or combination of columns composite that uniquely identi es a row Within a table Examples Student ID or ArtistCode Connecting with Computer Science Second Normal Form continued Determinant columns used to determine value assigned to another columns in the same row Example ArtistCode determinant for ArtistName Functional dependency Determinant and columns that it determines Each value of rst column matched to single value in second Example ArtistName functionally dependent on ArtistCode Connecting with Computer Science Second Normal Form continued Second normal form 2NF First normal form and Non PK columns functionally dependent on PK Creating 2NF Determine which columns not dependent upon PK Remove such columns and place in new table Default 2NF Table Without composite PK Chief 2NF benefit save disk space Connecting with Computer Science Figure 810 ZNF Remove any columns that are not dependent upon the primary key and create a new table Second quotml Fom1 Ida Songs Table BB Sloop John B 45 7 Vinyl CLA BBK The Thrill is Gone 93 8 MP3 BLU NIRV Come AsYou Are 24 3 MP3 NW NIRV L nhi Lrn 24 5 M P3 NW OB Only a Lad 78 16 CD NW PG Big Time 10 14 CD POP P G Sledgehammer 1 D 3 CD PO P TK American Soidier 91 1 3 MP3 CW T K Beer For My Herses 828 5 M P3 CW U2 One Tree Hill 1 46 ET CD NW U2 With or Without You 1 46 3 CD NW Anids Table Albu39n Table must m m BB The Beach Boys 1 o Shaking the Tree BBK BB King 2 1 N everrnind NIRV Nirvana 45 Pet Sounds OB Oingo Boingo 78 Best 0 Boingo P G Peter Gabriel 98 The Best of BB King TK Toby Keith 1 46 The Joshua Tree U2 U2 825 1U nleashed Q1 1 Shock39n Y all Connecting with Computer Science Third Normal Form 0 Third normal form 3NF Eliminate transitive dependencies Transitive dependency column dependent upon another column not part of PK Example GenreDesc depends on Genre Code Each nonkey eld should be a fact about the PK Connecting with Computer Science Figure 81 1 Songs table with the GenreDesc column added Songs labls mama scram iwmnrwm momma comm smear BB Sloop John B 45 T Vinyl CLA Classic Rock Ball The Thrill is Gone 98 8 MP3 BLU Blues NIRV Come As You re 24 3 MP3 NW NewWaue NlRV Lithium 24 5 MP3 NW NewWoie OB Only a Lari 73 13 CD NW NewWouo PG Big Time 10 14 CD POP Popular PG Sledgehammer iii 3 CD POP Popular TK merioan Soldier 911 3 MP3 CW CourriryWestem TK Beer For MyHorsas 325 5 MP3 CW CourtiryWestem U2 One Tree Hill 145 9 30 NW NewWaue U2 With Without You 145 3 CD NW NewWave Connecting with Computer Science Third Normal Form continued Creating 3NF Remove transitive dependencies Place removed columns in new table 0 Chief 3NF bene t save disk space By 3NF level following new tables created Genre Artists Album Connecting with Computer Science Figure 8 12 Songs and Genre tables in 3NF Til39w39rd Normal Fortunate Songs table 33 Sloop John El 45 339 Vinyl CLA BEIK The Thrill is Gone 98 B M P3 EILU NIRV Come s r ou Are 24 3 MP3 NW NIRV Lithium 24 S M P 3 NW 05 Only a Lad 1 8 1 B CD NW PG Big Time 10 14 CD POP P G Sledgeham mer 1 El 3 CD P0 P T K Am erican Soldier 91 1 3 M P 3 CW T K Beer For M 5 Horses 826 5 M P 3 CW U2 One Tree Hill 1 4B 9 CD NW U2 With or Without You 1 4B 3 CD NW Gene Twila BLU Blues CLA Cl assi c R oak CW C ounlry Westem NW NewWave P 0P P opular Connecting with Computer Science Figure 813 Eliminating repetition saves storage space Third annel Forml ate Songs Table BB Sloop John B 45 F Vinyl OLA BBK The Thrill is Gone 98 8 MP3 BLU NIRV Come As You Are 24 3 MP3 NW NIRV Lithium 24 5 MP3 NW 03 Onlye Led 78 16 CD NW PG Big Time 10 14 CD POP P G Sledgehammer 10 3 CD POP TK merican Soldier 911 3 M P3 CW TK Beer For My Horses 826 5 MP3 CW U2 One Tree Hill 146 9 CD NW uz With or Without You 146 3 CD NW Gene Tmle Artists Ta e Album Table Ger20m BLU Blues BB The Beach Boys 10 Shaking lhe Tree CLA Classic Rock BBK BB King 24 Nevermind CW CounlryWeslem NIRV Nlrvana 45 Pet Sounds NW NewWave OB Oingo Eloingo 7398 Best 0 Boingo P 0 Popular PG Peter Gabriel 96 The Best of B Be King T K Toby Keith 146 The Joshua Tree U2 U2 826 U nleashed 911 Shock39n Y39all Connecting with Computer Science The Database Design Process 0 Six steps to designing normalized database 0 Example Creation of student grading system Connecting with Computer Science Step 1 Investigate And De ne Investigate and research info to be modeled 0 De ne purposes and uses of the database Use any documents end user works with to complete tasks Involve the end user in design process Student grading system based on a course syllabus Connecting with Computer Science Step 2 Make a Master Column List Create a list of elds for information Field properties might include such items as Field Name Data type char varchar number date etc Length Number of decimal places if any Review users documents for elds Forms and reports good source for elds Example elds Student ID First Name Last Name Connecting with Computer Science Step 3 Create the Tables 0 Logically group de ned columns into tables Heart of the design process Relies heavily upon the normalization rules Main rules in database design INF 3NF A table in 3NF is well de ned 0 Normalizing databases is like cleaning a closet Connecting with Computer Science Figure 8 14 Tables created for the student grading system 4in I StudentlD LastName EMail E il La le39sil 717 39239 n ii in lwl39ii 1 Iiijiu 1quot1 7 223212578 Squi r Tentacles Squidward wsuedu So 543768893 Yosemite Sam Yosemitewsuedu Ju 984367183 Bugs Bunny Bugswsuedu Sr are Record M 1 LIME of 4 Jam CourselDI StudentlD TestAve IFrnaLExam FInaLGrade Le erGrade L 1829388133 111111111 70 65 80 72 67 3 1829388133 223212578 80 85 90 86 85 B 1829388133 984367183 96 92 94 98 95 A 2731288812 543768893 84 82 86 78 83 B 3712883828 223212578 100 90 88 92 93 A 3712883828 984367183 100 98 98 96 98 A 4311223112111111111 60 70 82 80 730 4311223112 223212578 82 84 78 90 84 B are 0 0 0 0 0 Record M l M of 3 am agm GradeLevel I GradeDesc I I ICourselD CourseDesc I L Fr Freshman 1829388133 Underwater Programming Ju Junior 2731288812 Intro to Computer Science So Sophomore 3712883828 Software Engineering for Cartoons Sr Senior 43112231 12 Database Design in aToon Environment Tml11i 1 gtgtIgtIof4 Record MI 1 DIDIID39IIOF1 Connecting with Computer Science Step 4 Work On Relationships 0 Relationship de nes table relations Two types of relationships discussed in this chapter Onetomany 12M Onetoone 11 Primary and foreign keys de ned in each of the tables Primary key PK determinant discussed earlier Foreign key FK column in one table is PK in another Following sections describe how PK and FK function Connecting with Computer Science Step 4 Work On Relationships continued OneToMany le Most common relationship States that each record in Table A relates to multiple records in Table B Requires that FK columns in many table refers back to PK in one table Example Grades Table to Student Table Connecting with Computer Science Figure 815 The relationship of Grades to Students is enetomany 1M M quot Student 2 c 1 1 2 Grades Emirates Student 2 a Student Connecting with Computer Science Step 4 Work On Relationships continued Onetoone 11 Dictates that for every record in Table A there can be one and only one matching record in Table B Consider combining tables in 11 relationship 1 1 sometimes appropriate each student has one grade level Student Table to Grade Level Table FK columns in one table PK columns in the other one table Connecting with Computer Science Figure 816 The relationship of Student to Grade Level is onetuone 11 It I D Student Gradehwel Connecting with Computer Science Step 5 Analyze The Design Analyze the work completed Search for design errors re ne the tables as needed Follow the normalization forms ideally to 3NF Correct any Violations ER models Visual diagram comprised of entities and relationships Entities represent the database tables Relationships show how tables relate to each other Cardinality shows numeric relations between entities Connecting with Computer Science Step 5 Analyze The Design continued 0 Types of cardinality and their notation include O1 01 zero to one OM OzN O On zero to many 11 11 one to one 1M 1M1N 1 1n one to many M1 M21 N21 1 n1 many to one MM MzM NzN nn many to many Example an ER model for the studentgrading system Connecting with Computer Science Figure 817 The studentgrading system ER model in Visio Student Grades PK Student ID PKFK1 QauraeJD 1M I 391 FursLName 1 1 M PK39FKz r LasLName J E Mail Courses 3323 Fm GradesLevel PK Coum ID aims 1 Final Exam mum Dem Final rade 1 ILBII BLGI39QUB Gradelml PK grade Laval GradaaDasc Connecting with Computer Science Step 6 Reevaluate Reevaluate database performance Ensure database meets all reporting and form needs Include the end user Explain each of the tables and elds being used Make sure elds are de ned to user s requirements Manipulate data structure with SQL commands Connecting with Computer Science Structured Query Language SQL Structured Query Language SQL functions Manipulate data De ne data Administer data Many different dialects of SQL SQL commands can be uppercase conventional or lowercase Connecting with Computer Science Structured Query Language SQL continued SQL provides the following advantages Reduces training time syntax based in English Makes applications portable SQL is standardized Reduces the amount of data being transferred Increases application speed 0 Following sections show basic SQL commands Creating tables Adding inserting rows of data Querying table to select certain information Connecting with Computer Science Figure 818A sample SQL statement and results SQL statement SELECT SongJJame ArtisLCode AlburrLNum Tracleum MedlaType FROM Songs WHERE GenreECode quotNNquot Result El Sump Um ry 39 wlml Uurry Song mo Ma a AlbunLNum TmLNum With DtW39Ithout YtIu U 148 3 CD One Tree Hull U2 14E 9 CD Only a Lad DB 78 16 CD Lithium MW 24 5 MP3 Came As You Are NIFN 24 3 MP3 0 U Record vnlnal 5 Connecting with Computer Science CREATE TABLE Statement CREATE TABLE statement make new table Syntax CREATE TABLE tablename columnname datatype NULL NOT NULL columnname datatype NULL NOT NULL NULLNOT NULL Optional property indicates Whether data required Connecting with Computer Science CREATE TABLE Statement continued 0 Following SQL statement creates table called Songs CREATE TABLE Songs SongName char 50 NOT NULL AlbumNum number NOT NULL ArtistCode char 5 NOT NULL TrackNum number NULL MediaType char 5 NULL GenreCode char 5 NOT NULL Connecting with Computer Science INSERT Statement INSERT statement add new rows of data Syntax INSERT INTO tablename columnl column2 VALUES constantl constant2 INSERT statement requires a table name Square brackets specify optional columns Columns on separate lines for readability Connecting with Computer Science Figure 819 SQL INSERT statement to add a record to the Songs table and its resuit SQL statement INSERT INTO Songs Song ame AlbumNurn ArtisLCode GenreCode VALUES quotWhere the Streets Have No Namequot 146 quotU2quot quotPDPquot Resulit flll aunqs luhlr in m American Soldier 91 i 3 MP3 TK CW Beer For MyHorses 325 5 MP3 TK CW g Big Time 10 14 CD PG POP Come As You Are 24 3 MP3 NIRV NW Lithium 24 5 MP3 NIRV NW One Tree Hiil 14E 9 CD U2 NW Oniy a Lad 78 18 CD 08 MW e Sieogehammer In 3 OD PG POP Sloop John B 45 T Vinyl BB CLA i The Thrill 13 Gone 98 8 MP3 BBK BLU Whene the Streets Have No Name 146 l U POP With or Without You 145 3 OD U NW 9 D I em MI H L e n of i2 Connecting with Computer Science SELECT Statement SELECT statement retrieves data from one or more tables Syntax SELECT DISTINCT columnlist FROM tablereference WHERE searchcondition ORDER BY orderlist Speci ed order determines order of retrieval display Connecting with Computer Science Figure 820 SQ39L SELECT Statement to return song39s name media type and track number SQL svt ateime nt SELECT SongName MediaType TrackNurn PROM Songs Result E SeIcct2 Select Query SongJ diame MediLType American Soldier MP3 3 Beer For My Horses MP3 5 Elin Time CD 1I4 Come As You Are MP3 3 Lithium MP3 5 One Tree Hill CD 9 Cir113r 3 Led CD ll Sledgehammer CD 3 Sloop John B Vinyl 739 The Thrill is Gone MP3 8 VVlhere the Streets Have No NE With or Without You CD 3 gt El Rmd Idldll 13 v uw39ofm3 Connecting with Computer Science WHERE Clause 0 WHERE clause Speci es additional criteria for retrieving data Fields should be included in elds selected AND and OR keywords Allow speci cation of multiple search criteria AND indicates that all criteria must be met OR indicates only one criterion needs to be met Connecting with Computer Science Figure 321 SQL SELECT statement using a WHERE clause and its resu t SQL statement SELECT SmgName TradltINum FROM Songs WHERE Medl aTvpe CDquot39 Result 39 If i EunLNam T kNum With or Wth ul You 3 E One Tree Hill 9 Only a Lad IE Sledgehammer 31 L Bug Time 14 itquot D nmd M1 5 inbinf5 Connecting with Computer Science Figure 822 More descriptive SQL SELECT statement using a WHERE clause SQL statement SELECT SongNarne Medlajype TrackNum FROM Songs WHERE MediaType quotCDquot result 55 Queryl Select Query SongName MbdiLType Trac kNum With or Without You CD 3 I One Tree Hill CD 9 I Only a Lad CD 15 I Siedgehammer CD 3 ll Big Time CD 14 E o Retard H 4 r imwiof 5 Connecting with Computer Science Figure 823 SQL SELECT statement using a WHERE clause with AND versus OR SQL statement SELECT Songllame Medlejype Traciltl lum FROM Songs WMediaEType CD DR Media Tvpe quotMP3quot AND TrackNum gt g result 1 I l I El x SongName Mediajype Tracleum L One Tree llill CD 9 Only a Lad CD 18 Big Time CD 14 The Thrill is Gone MP3 9 El Recurd uh 1 PIDI IDIhlofd Connecting with Computer Science ORDER BY Clause 0 ORDER BY clause Permits you to change how the data is returned Makes for more meaningful presentation By default the data is returned in sequential order 0 You can specify the ORDER BY column names 0 ORDER BY also returns data in ascending default or descending order Connecting with Computer Science Figure 824 SQL SELECT statement using an ORDER BY clause and its result SQL statement SELECT Songllame Track lNum MediaTypze FROlVI Songs WHERE Medlajype quotCDquot OR MlediaTyple quotMP3quot AND TrackNum gt 6 ORDER BY SongLName TrackNum result 15 Queryl Select rst Sonnglame ri39lrlracLNum i H MedtqTypei Big Time 14 CD One Tree Hill 9 CD Only a Lad l6 CD The Thrill is Gone 8 MP3 I Record HI 1 II 1 2 InInl of 4 Connecting with Computer Science Figure 825 SQL SELECT statement using an ORDER BY clause with the default ascending option SQL statement SELECT SongName TrackNum MediaType FROM Songs WlIERE MediaTvpe CD OR Media l39ype 39MP3quot AND TrackNum gt 6 ORDER BY TradkNum SongName result if I 39 I D Song Namo TrackiNlum Modia ll39mpo l The Thrill is Gone 8 MP3 One Tree Hill 9 CD Big Time 14 CD Only a Lad 16 CD 5 D Record I1 ll 1 pwlmor4 Connecting with Computer Science Figure 826 SQL SELECT statement using an ORDER BY clause with the DESC option SQL statement SELECT SongName Trackllum MediaType FROM Songs WHERE MediaType quotCDquot OR MediaType quotMP3quot AND TrackNum gt 6 ORDER BY TrackNum SongName result 5331 Query Select Query X Sonngame TrackNum MedlLType l The Thrill is Gone 8 MP3 One Tree Hill 9 CD J Big Time 14 CD Only 3 Led 16 CD ale 0 Record I4 1 D IN hutIi of 4 Connecting with Computer Science ORDER BY Clause continued Many more options can be speci ed on SELECT statement 0 Many more SQL commands used to maintain de ne administer data found Within a database Connecting with Computer Science Summary Database collection of logically related records DBMS software used to design manage interface With databases 0 Indexes les that revise default sequential order of data 0 Normalization process of removing data redundancies Connecting with Computer Science Summary continued Data normalized with ve normal forms 0 First three normal forms most important Primary key uniquely identify table entries 0 Foreign key primary keys in other tables 0 Entity relationship model Visual diagram of tables and relationships Connecting with Computer Science