exam
ICT105 – Data Modelling and Database Systems ICT105 – Data Modelling and Database Systems Week 2- Lectorial The Relational Model: Introduction, QBE, and Relational Algebra Friedrichsen, L., Ruffolo, L., & Monk, E. (2020). Concepts of database management. ISBN: 9780357706176 TEQSA: PRV14311 CRICOS: 03836J TEQSA: PRV14311 CRICOS: 03836J ICT105:Data Modelling and Database Systems Unit Coordinator: Ms. Ashwini Shivabasappa Lecturer: Ms. Ashwini Shivabasappa • Consultation hours: Monday 12pm-1pm • Class Time Lectorial : Monday 11am-12pm Webinar A: 9am-11am Webinar B: 1pm-3pm • Email:
[email protected] TEQSA: PRV14311 CRICOS: 03836J Copyright Disclaimer All staff and students are required to abide by the Copyright Act 1968, when providing, accessing and using learning resources and materials. Some resources are provided under the statutory licence, and you are required to see this notice: WARNING This material has been reproduced and communicated to you by or on behalf of Sydney International School of Business and Technology in accordance with section 113P of the Copyright Act 1968 (Act). The material in this communication may be subject to copyright under the Act. Any further reproduction or communication of this material by you may be the subject of copyright protection under the Act. Do not remove this notice. TEQSA: PRV14311 CRICOS: 03836J Recap of the Previous Webinar The following objectives were covered in the previous webinar: • Examine JC Consulting (JCC), the company used for many of the examples throughout the text • Define basic database terminology • Describe database management systems (DBMSs) • Explain the advantages and key factors for a healthy relational database system • Prepare for a career in database administration TEQSA: PRV14311 CRICOS: 03836J Learning Objectives • Describe the relational database model • Explain Query-By-Example (QBE) • Use criteria in QBE • Create calculated fields in QBE • Summarize data by applying aggregate functions in QBE • Sort data in QBE • Join tables in QBE • Update data using action queries in QBE • Apply relational algebra TEQSA: PRV14311 CRICOS: 03836J Examining Relational Databases • A relational database is a collection of tables • Formally, these tables are called relations • Each entity is stored in its own table • The JCC database has a table for employees, for clients, and so on • The attributes of an entity become the fields or columns in the table • The table for employees, for example, has a column for the employee ID, a column for the employee’s first name, the employee’s last name, and so on TEQSA: PRV14311 CRICOS: 03836J TEQSA: PRV14311 CRICOS: 03836J Examining Relational Databases • Relational database shorthand • You write the name of the table and then, within parentheses, list all the columns in the table • Each table should appear on its own line • When a database has duplicate column names, you need a way to indicate the column to which you are referring • Write both the table name and the column name, separated by a period • Primary key is a column or columns that uniquely identifies a given row in that table • A composite key is a unique identifier consisting of more than one column • A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables TEQSA: PRV14311 CRICOS: 03836J Creating Simple Queries and Using Query-By-Example • A query is a question structured in a way that the DBMS can recognize and process • Query-By-Example (QBE) is a visual GUI (graphical user interface) approach to writing queries • Users ask their questions by dragging column names to a grid as opposed to writing commands from a keyboard • The upper portion of the window contains a field list for each table used in the query • The lower pane contains the design grid, the area in which you specify the fields to include in the query results, a sort order for the query results, any criteria, and other instructions TEQSA: PRV14311 CRICOS: 03836J Tasks for the Week • Read Chapter 2 from the textbook. • Submit Tutorial 1 Exercise from week1. • Create basic queries in access using the database available in LMS. TEQSA: PRV14311 CRICOS: 03836J Any Questions? Study material/ict105-week10-lectorial-ryomn2ms.pdf ICT105 – Data Modelling and Database Systems Week 10- Lectorial Database Industry Careers Friedrichsen, L., Ruffolo, L., & Monk, E. (2020). Concepts of database management. ISBN: 9780357706176 TEQSA: PRV14311 CRICOS: 03836J TEQSA: PRV14311 CRICOS: 03836J ICT105:Data Modelling and Database Systems Unit Coordinator: Ms. Ashwini Shivabasappa Lecturer: Ms. Ashwini Shivabasappa • Consultation hours: Monday 12pm-1pm • Class Time Lectorial : Monday 11am-12pm Webinar A: 9am-11am Webinar B: 1pm-3pm • Email:
[email protected] TEQSA: PRV14311 CRICOS: 03836J Copyright Disclaimer All staff and students are required to abide by the Copyright Act 1968, when providing, accessing and using learning resources and materials. Some resources are provided under the statutory licence, and you are required to see this notice: WARNING This material has been reproduced and communicated to you by or on behalf of Sydney International School of Business and Technology in accordance with section 113P of the Copyright Act 1968 (Act). The material in this communication may be subject to copyright under the Act. Any further reproduction or communication of this material by you may be the subject of copyright protection under the Act. Do not remove this notice. TEQSA: PRV14311 CRICOS: 03836J Recap of the Previous Webinar • Identify the services provided by a database management system • Examine the catalog feature in a DBMS • Describe the concurrent update problem • Explain the data recovery process in a database environment • Describe security services • Examine data integrity features • Discuss data independence • Define and describe data replication TEQSA: PRV14311 CRICOS: 03836J Learning Objectives • Explore and describe jobs and careers in the database industry • Summarize the policy-making, technical, and administrative responsibilities of a database administrator (DBA) • Define the job titles and responsibilities for employees who may report to the DBA • Describe the responsibilities of a data analyst • Explain the responsibilities of a data scientist • Explore the educational credentials and industry certifications that support a career in the database industry TEQSA: PRV14311 CRICOS: 03836J Careers in the Database Industry • “Traditional” operations of a business historically identified with the acronyms BICARSA and GLAPPR • Internal processes of billing, inventory control, accounts receivable, sales analysis, general ledger, accounts payable, and payroll • Today, however, new careers on the data analysis side of the database industry have emerged • Data analysts: professionals whose focus is to analyze internal data and relationships among data • Data scientists: people employed to interpret complex and very large sets of data, are in high demand TEQSA: PRV14311 CRICOS: 03836J Careers in the Database Industry • Business leaders want quick and reliable access to internal data, but also have an unquenchable appetite for relevant external information • External data can be collected digitally, including big data • This data also has relevance to business decisions, but it is often not stored in a company’s internal, centralized relational database • Big data typically is too large, is too varied, and changes too quickly to be managed by a traditional relational database system • A rewarding career in the database industry relies on a combination of relevant educational credentials, industry certifications, and a track record of successful experience TEQSA: PRV14311 CRICOS: 03836J Role of a Database Administrator • The database administrator (DBA) is responsible for organizing, managing, and securing an existing database • Reports to the CIO (chief information officer) or the president • The responsibilities of the database administrator vary • Helps define and model data requirements, business rules, and database policies • Works closely with software application developers • Involved with installing, configuring, and testing database hardware and software • Responsible for security of the database and the disaster recovery plan TEQSA: PRV14311 CRICOS: 03836J Role of a Database Administrator TEQSA: PRV14311 CRICOS: 03836J Database Administrative Functions • The DBA manages the data dictionary • Establishes naming conventions for tables, fields, and indexes • Creates the data definitions, as well as for any data integrity rules and user views, for information stored in the data dictionary • Creates and distributes appropriate reports from the data dictionary to users, programmers, and other people in the organization • The DBA is responsible for training • Coordinates the training and ongoing education of users and the technical staff responsible for developing and maintaining database applications TEQSA: PRV14311 CRICOS: 03836J Professionals Reporting to the DBA • Given the widespread activities and responsibilities of a DBA, in large organizations, these tasks are handled by employees who report to the DBA • Application DBA • System DBA • Task-oriented DBA or database operations manager • Performance analyst • Database architect • Data modeler • Database analyst • Research analyst TEQSA: PRV14311 CRICOS: 03836J Professionals Reporting to the DBA • Given the widespread activities and responsibilities of a DBA, in large organizations, these tasks are handled by employees who report to the DBA • Business analyst • Product manager • Data warehouse administrator • Data scientist • Data visualization expert TEQSA: PRV14311 CRICOS: 03836J Responsibilities of a Data Analyst • Data analyst skills • Completes statistical and graphical analysis of the following: • Internal data such as sales figures • Cost of goods and services • Transportation expenses • Other operational expenses • Helps an organization make faster and better decisions • Related job titles include the following: • Entry-level data analyst • Data analyst intern • Analytics TEQSA: PRV14311 CRICOS: 03836J Responsibilities of a Data Scientist • Data scientist skills, experience, and insight • Analyzes massive sets of internal, external, and big data • Spent a considerable amount of time being a data analyst or statistician • Skilled with programming algorithms and the application of statistics to analyze large sets of data • Looks for patterns and trends to discover new opportunities and solutions • Related job titles • Data scientist intern • Quantitative analyst • Senior data scientist TEQSA: PRV14311 CRICOS: 03836J Summary • Job and career growth in the database industry is strong and predicted to remain strong over the next decade. • Traditional database industry jobs terminate in the position of DBA (database administrator) or even CIO (chief information officer). • Responsibilities of a DBA span from policy-making decisions such as data retention, destruction, and compliance through deep technical skills in data design, SQL, testing, and maintenance, to management and administrative responsibilities such as documentation and training. • Responsibilities of employees who often report to the DBA vary from maintaining and monitoring internal databases to working on the data analysis side of both internal and external data. TEQSA: PRV14311 CRICOS: 03836J Summary • A data analyst’s primary responsibility is to analyze internal data and transactions to identify and communicate important relationships,