databse and filetypes Flashcards
(45 cards)
1NF
atomic data
must have primary key
There are no columns with repeated or similar data
benefits of normalization
Atomic data is easier to search on specific criteria
easier to modify data as one record will update related records
no redundant data
file size is smaller
No repeated data so less errors
Opensource file format
format for storing data for anyone to use
defined by a publicly published specification
can be used by both proprietary and open source
often called free file format as not covered by copyright
specifications are maintained by a standards organization
(CSV/TXT/JPG)
(smaller size sometimes)
needed as can work in multiple software
provides standard file type
Data Dictionary contains?
table name
field name
data type
field length
field default value
Description of each field
Required ( if it can be left blank)
primary key/foreign key
relationships
validation
table security ( who can delete and edit)
Static parameter query adv and explanation
fixed criteria which is decided when written
saves time running the query
less likely for error as no input required
more user friendly as don’t have to enter search criteria (than dynamic)
data types
– text, alphanumeric
– numeric (integer, decimal,currency)
– date and time
– time
– percentage
– Boolean/logical (yes/no, true/false)
Static parameter query disadv
Every time query is run will search for same data
-cannot be changed
-can end up having lots of static query
-making it hard to find query u want
If different data is searched for user would have to open in design view and change the data in the criteria to required
Static queries increase file size ( vs dynamic)
Dynamic parameter query explanation adv/dis
Can be used to search for different values each time its run
-more flexible
-uses a dialog box for user to enter data required
Saves time designing queries as you don’t need to make similar queries ( vs static)
Requires less technical knowledge of the user ( vs static)
indexed sequential access
-Allows records to be accessed in order they were entered (or randomly)
-Each record is given a index
-Data is organized into records which consist of fixed field lengths
-uses a set of hash tables which contains “pointers” into the records
-Can be searched quickly
-Searches uses an index which narrows down records to be searched and then that section of file is searched sequentially to find the record required
Foreign key
primary key from another table
used to create relationship between tables
referential integrity
forces table relationships to be consistent
enforces table relationships
ensure both sides of relationship exist
ensures that foreign key references primary key
why referential integrity is important
ensures that foreign key is valid
avoids redundant data
avoids orphan data
warns deletions of related records
data within the related field cannot be changed
relational database
has more than one table
each table holds data that is related
relationships are established between tables using primary key,foreign key,compound key
made using one to one or one to many
can create queries/reports with more than 1 table
csv adv
comma separated value
human readable
can be edited in text editor
processed by almost all programs
small in file size
csv disadv
includes values only
no formatting
lack of universal standard
problems when importing to SQL
poor support of special characters
many to many relationship
cannot exist/not possible to create
need to create a link table
made using 2 one to many relationships
each primary key exists as a foreign key in the link table
2NF
(only do this if u have a compound key)
no partial dependencies
The table must already be in first normal form
3NF
Must already be in 2NF
no non-key dependancies
like city depends on the country
MIS (management information system) how used
Used in decision making
used to coordinate data in a business
Used to analyze information
May be used on a database which can be queried
can be used to create reports, graphs and charts
by comparing reports to previous report trends can be identified
Hierarchical data base system
Uses a structure that defines level of access to records
uses one to many relationship
links are based on dependencies
Links a number of records to one parent
Inflexible and limited by only using one type of relationship
so its only used in specific uses
Normalization dis adv
Can take longer to set up database
more complex database due to relationships
has more tables so setting up queries can be more difficult so harder to keep track of data
may require greater expertise
making data atomic may not always be best solution as DOB can be separated but serves no purpose
MIS (management information system)
definition and contains
a computer based system which provides managers with tools to organize departments within a business
contains
database
hardware resources
decisions support systems
human resource management systems
Accounting information management system
Marketing information system
Executive information system
normalisation definition
a method of organising data tables
attempts to avoid data loss, data redundancy
Breaks data from non atomic structure to atomic
a multistep process where each step increases the efficiency of the database
Only related data is stored in a table
relational database adv
reduced chance of data entry errors
does not have redundant data
uses less storage space
related fields are updated when data is changed
easier to produce reports
easier to maintain security of data
