Flashcards
11: Databases
Cards in this deck (58)
- 1What are the limitations of file-based approach 5- data integrity problems in single file: no validation techniques - dont allow dublications or inconsistent
- data pricavy: no access rights
- data redundancy and possibly inconsitench: once stored in 1 file shouldn't store again in dif file
- data dependency concerns: which porgrams access file: programmers need to know how data organised in file. When data re-written, changed, prgrams need to be changed/re-writeten. File strucutres defined to suit specific programs not new
-handling data difficult: inserting, updataing, detecting
- def of data embedded in application
-changing table strcture dificult - 2Data RedundancyThe same data stored more than once.
- 3RelationThe special type of table which is used in a relational database.
- 4AttributeA column in a relation that contains values.
- 5TupleA row in a relation storing data for one instance of the relation.
- 6relational databse notesrelation - table
attribute - element / colomn
tuple - row: set of atomic values: each attribute has 1 value or no value: principle
Often rows called record and colomns fields - 7Primary KeyAn attribute or a combination of attributes for which there is a value in each tuple and that value is unique.
- 8Candidate KeyA key that could be chosen as the primary key.
- 9Secondary KeyA candidate key that hasn't been chosen as the primary key.
- 10Foreign KeyAn attribute in one table that refers to the primary key in another table.
- 11Referential IntegrityThe use of a foreign key to ensure that a value can only be entered in one table when the same value already exists in the referenced table.
- 12What is stepwise refinement?Top-down method to break down database design simply - at each step more detail added to design. Uses E-R diagram.
- 13What is the Entity-Relationship Model What is a entity? What is a cardinality?shows relations/structure of data. Identifies: entities, attributes, primary and foreign keys.
Entity: a thing, tpe of person, event, transaction or organisation. Must be number of instances of the entitiy. Entity will become a table in a relational database.
Cardinalities - relationships - 14Note go over e-r relation modellingArrows, circles, |
- 15What each represent? 1:M 1:1 M:M Give diagram for each1:M no further refinement, relationship shows entity at many end needs to have a foreign key referencing primary key of entity at one end. (box -<- box)
1:1 (box - box) options for implementation (dont consider)
M:M (box ->--<- box) foreign key cant be used - conttribute only handle single value can't handle many references soludtion create link enitity break up to 1: M by adding entity. Can have 2 foreign keys in linke entiy: each representing primary keys
(go over practice quesions) - 16Normalisation (different meaning in data representation)Design technique for constructing a set of table designs from a list of data items - also used to improve an existing table designs.
- 17Repeating GroupA set of attributes that have more than one set of values when the other attributes each have a single value.
- 18***** go over normalisaiton questiosnfind hard
- 19Mormalisation 3 steps what first step 1NF1. Conversion to first Normal form: s[litting data into 2 groups - represent data as table definitions - have to choose table names and identify primary key for each table, one table contains the non repeating group attributes, oher repeating group
no repeating data - 20Describe sendond step 2NFConversion to second normal form: examing each non-key attribute and ask if dependent on bother parts of compund key. If dependnt on only 1 attribute must be moved out into a new table. all non-key attributes dependednt on primary key
- 21descirbe third step of normlaisation 3NFConversion to third Normal form: look for any non-key attributes dependent on another non-key attribute if is new table defined.
No functional dependency between non-key attributes
An attribute depdns on the key, whole kye and nothering but key - 22Data Management System DBMSSoftware that controls access to data in a database.
(software application used to create, manage, administer databases ie access) - 23Database Administrator DBAA person who uses the DBMS to customise the database to suit user and programmer requirements.
- 24A database isnt just colleciton of data. There is a 3 level model What are the 3 levels?External level
Conceptual Level
Internal level - 25Give a diagram of the ANSI 3-level architecture for theatrical agenc database
- 26Describe storage (internal schema)onlyh known at internal level, lowset level in ANSI artchecture (American Naitonal Standards Institution) controlled by DBMS software. Programmers who rwrote software are only ones who know structure of storage of data on dsik
- 27Describe Conceptual levelsingle universal view of databse. controlled by DBA who has access to DBMS
- 28describe exxternal levelindividual and programmer views. Each view has external schema descirbing which parts of database are accessible. A veiw can support number of user programs. Provision of views - ensure security - indidivuals given access rights to control actions allowed for view.
- 29What are the facilities provided by a DBMS? 4- DBMS offers software tools through a developer interface, allows for tables to be created and attributes to be defined together with data types
- facilities for programmer to develop a UI
- provides query processor - allows query to be created and processed
-capability for creating a report to present formatted ouput. Programmer can incorporate access to quieres and reports in UI - 30QueryUsed to select data from a database subject to define conditions.
(mechanism for extracting and manipulating data from database) - 31Query ProcessorSoftware tool provided by a DBMS to allow creation and execution of a query.
- 32Developer InterfaceGives access to software tools provided by a DBMS for creating tables.
- 33What are the DBMS functions likely to be used by a DBA?DBA responsibly for setting up user and programmer views and defining access rights. DBA can see data ditionary - contains metadata about data. Can create Index for a table-needed if larger numbe rof attributes and tuples. Index table has attribute value and pointers to corresponding tuples in original table. Index can be on primary key or secondary ke. Much quicker using index
- 34IndexA small secondary table used for rapid searching which contains one attribute from the table being searched and pointers to the tuples in that table.
- 35why is integrity of data in database a concern?Translation - styem problem prevents completion - undefined state for database. DBMS should have built in feature to prevent occring. Regular backup requrement - DBA responsibly for backup of stored data
- 36What are hte Advantages of DBMS? 5Multiple views
consistency (not left in inconsistent state)
security
data persistence
query language filters
relation-based tables
reduced redundancy - normlaisation - 37What is Data Definition Lanaguage DDlpart of SQL for creating or altering tables. Commands only create the structure - dont put data in
No case sensititivity
list of rules seperated by comma
attribute defined 0 data type must be specified - defined how many characters can be used. - 38What does DML stand for? What are the 3 categories of use for DML? What main use of DML?Data manipulation language
- insertion of data into the table when database created
- modification or removal of data in databsae
- reading of data stored in database.
Main use of DML - obtain data using a query - always start with select command - 39Why use databases? 3 Why use over flatfile? 2data can be : retreived quickly, updataed, filtered, set user access rights + vies. Ofer flatfile: easier to manage/insert, updata, detete, remove duplicate data - reduce storage
- 40What is DBMS secuirty? 2access rights
refernetial integrity - 41What is DBMS Maintenance? 2backups
create/maintain indexes - 42Wha tis DBMS creation and use of? 5DB structure (tables and relationships)
queries
views
intercae
outputs - 43*Go over SQL questions / sql language
- 44SQL commands: how to make a query and othersSELECT
FROM
WHERE
(ORDER B ___ ASC or DESC)
GROUP BY
SELECT
UPDATE
DELETE
INSERT INTO
CREATE DATABASE
ALTER DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE (deletes)
CREATE INDEX
DROP INDEX
_._ if more than 1 table - 45What is a databaseshared collection of logically related data defined and controlled indeptendednt of user applications. Data stored in bits usually magnetic storage mapped to logical model determines how data presented to data.
- 46Describe DBMSmanaging databases ,allows creation and manipulation of logical data strucutres, defines and contols access to different groupings or levels of data, transaciton processing - if part fails, data rolled back to beinging of transaction
- 47What are the Advantages of Databases? 6d-ata accessibility (without programming)
-Data Integrity: central databases has own integerity checkes ie no duplicate records or wrong data types linked
-Control of data redundancy as central store - no need to duplicate layers
- Sharing of data
- more and better info- links;
Improved security: backed up data, protected phycially and logically
- Easier maintance and upgrading - 48What are the dis of databases? 5ppl willing to share data and relinquish control of data to ownership of central database
- conectration of risk
Complexity
cost-expensive
performance - 49What is the security in databases? 5ENCRYPTION
authentication
priviledges
audit
backup and recovery - 50Describe ways in which acces rights can be used to protect data in database from unauthorised access 3• Access rights give managers / himself access to different elements
• ... by having different accounts / logins
• ... which have different access rights e.g. read only // no access / read /
write
• Specific views can be assigned to himself and to the managers
• ... e.g. managers can only see the data for their own shop(s) - 51learn register transfer notation for fetch execute cyclenote
- 52Describe why sound closer to original when a higher sample rate is used 2• Smaller time gaps between the samples
• Makes the digital sound wave more accurate
• Smaller quantisation errors - 53Describe functions of a Wireless Netwokr INterface Card 4• Provide interface to wireless network
• ... as an antenna
• Receives analogue radio waves
• ... convert them to digital / binary
• Checks incoming transmissions for correct MAC / IP address
• ... ignore transmissions not intended for it
• Encrypts / encodes the data
• Decrypts / decodes the data
• Takes digital/binary input and converts to analogue waves
• ... sends the radio waves via the antenna - 54Identify 2 possible devices that can be used to physcilaly connect computers to rest of networkrouter
switch
hub - 55First Normal form , 2nd, 3rd what mustno columns with repeated or similar data
each row unique with a primary key
Each field must have unique name
2nd: each fileds must rely on primary key to be unique
3rd: no field should rely on another non-key field. - 56Describe a WAPpiece of hardware which conencts to a switch and allows WiFi devices to connect to the internet. WAPs broadcast on SSID (network) so that wireless devices know which network to join. Important taht WAPs use encyrptioan and authentication so unauthroised individuals don't take advantage of network.
- 57Describe NICprovides a connection between evice and network. NIC will nake different form based on transmission media used.
- 58Describe a switchconnects together several dvices to form a wired network. Each computer on network has own unique address: MAC address. When data send on entwork, includes send and recipient address. Switch learns the MAC address of devices connected to it, so can send data to inteded destination only.
Information
- Description
- A Level AS Computer Science (Unit 1: Theory Fundamentals) Flashcards on 11: Databases, created by HRYN red on 03/01/2023.
- Created by
- HRYN red
- Views
- 1
- Rating