INFS1000 Lec 4 Databases and Information Flashcards
What are BUSINESS OBJECTS? Give some examples.
- Things, people, processes with more than 1 single instance that we want to collect info about.
e. g. Customers, products, suppliers, transactions, equipment, locations, staff.
What is the purpose of a DATABASE?
- To store and organize info on important business objects.
Why can’t spreadsheets do the same thing as a database?
- Spreadsheets can only keep list of a SINGLE business object (e.g. data about customers).
What does a database do? What does a database contain?
- Keeps lists of MULTIPLE business objects and their INTERRELATIONSHIPS (e.g. customers and their purchases).
Contains:
- Tables
- Relationships among tables
- Metadata
What is the definition of a database?
- a SELF-DESCRIBING collection of INTEGRATED RECORDS.
What are the 4 steps in the overall database process?
- User: has to solve a problem using data stored in the DB (e.g. Check schedule of classes for the semester).
- Database Application: software component allows user to analyse, organize, present, print and display data - this is what the user sees.
- DBMS: software component organizes the access to the DB: read, write, delete, change of data.
- DB: tables, relationships, metadata.
What are the INFS names for rows, columns, characters, tables?
Rows / Records
Columns / Fields
Characters / Bytes
Tables / Files
What is the HIERARCHY of data elements in a table?
- Bytes / characters are grouped into columns / fields.
- Columns / fields are grouped into rows / records.
- Rows / records are grouped into tables / files.
How do RELATIONSHIPS exist among tables?
- Relationships exist between ROWS in different tables. These implement and represent BUSINESS RULES.
- Relationship between rows are linked by KEYS (primary + foreign).
What are PRIMARY KEYS?
- Column / group of columns that uniquely identifies a row in a table.
What are FOREIGN KEYS?
- Columns that are primary keys in other tables.
What are RELATIONAL DATABASES?
- Databases using tables, primary keys, and foreign keys.
What is METADATA? Give some examples.
- Data about the data!
e. g.
* Field name (what is the column called).
* Data type (text, no, date DD/MM/YY etc).
* Field properties (e.g. length).
* Description (what is in it?).
What is a DATABASE MANAGEMENT SYSTEM (DBMS) and what does it do?
- Program used to create, process, and administer a database.
- Typically uses SQL (structured query language) and licensed from vendors. (e.g. Oracle, MySQL).
How is a database different from a DBMS / Database app?
- Database is a collection of tables, relationships and metadata (i.e. a big file on a HARD-disk).
- DBMS and apps are a SOFTware programs.
What is a DATABASE APPLICATION?
- Collection of (1) forms, (2) reports, (3) queries and application programs that make use of a database (e.g. SAP).
e. g. order entry application, purchasing application.
What do (1) forms, (2) reports, (3) queries in a database application do?
- Forms: used to read / insert / modify / delete data.
- Reports: calculates and shows data in a structured context for analysis. (e.g. ave age).
- Queries: are a means of getting answers from database data. Queries feed into reports.
What is ER MODELLING? What NOTATION do we use?
- Graphically representing business objects (entities) and the logical relationships between them.
- Chen’s notation.
What are the 4 elements in Chen’s notation? Give a definition and an example.
- Entity: Concept (people, places, things etc) which you wish to store info. (e.g. Tour guide).
- Entity Instance: Single occurrence of entity type (e.g. Tony Chan).
- Attribute: Characteristic of an entity type (e.g. Guide_ID, Guide_Name).
- Primary key: attribute that uniquely identifies each instance of an entity type. (e.g. Guide_ID)
What are the corresponding elements in the database?
- Entity, 2. Entity instance, 3. Attribute
- Entity –> Table
- Entity instance –> Row
- Attribute –> Column
What are the rules for the notation of an ENTITY?
- Rectangle
- All capital letters
- Stated in SINGULAR (e.g. CONTRACTOR not CONTRACTORS).
- Noun
What are the rules for the notation of ATTRIBUTES?
- Oval
- Capitalize each word
- Underscore between words
- Primary Key is also underlined
- Noun
What are the rules for the notation of a RELATIONSHIP?
- Diamond
- No capitals, can use spaces
- Verb (e.g. operates)
What are the 2 types of relationships?
- Relationship (Type): an association between one or more entities. (i.e. Link between 2 tables). (e.g. CONTRACTOR operates TOUR).
- Relationship (Instance): Association between entity instances. (i.e. Link between 2 table entries). (e.g. Tony Chan operates April Murray River Tour). [Primary - foreign key].
What are the 3 types of CARDINALITY?
One to many (1:M) = (M:1)
Many to many (M:M) - two (1:M) relationships.
One to one (1:1) - e.g. 1 customer is allowed to store 1 drivers license - treat as (1:M) in INFS1000.
Give an example of a (1:M) relationship description?
“A department can hire may tutors, each tutor is hired by 1 department”
What is the GOLDEN RULE for modelling relationships?
- The PK of the 1-side entity is always the FK of the M-side entity.
!!!!! PK of associative entity is also its FK’s !!!!!
What are the 2 reasons why we use two (1:M)’s rather than (M:M)?
1 - M - M - 1
- (M:M) will create many INEFFICIENCIES in the database.
2. PK’s will not be unique!
What are the rules for the notation of an ASSOCIATIVE ENTITY?
- Diamond Square
- Capital letters, underline connector.
- ADVISER_ STUDENT, or just OFFER.
- Composite primary key
