Database Star Schema

In: Computers and Technology

Submitted By quwenhga
Words 401
Pages 2
Information Systems 3 (database 3) Star Schema Due 19/06/2014
You must create a Star Schema University Attendance database for the following situation:
The database must record attendance of students in classes, the time of the lecture, the lecturer who taught the class, the room that was used and the subject for that lecture. For each subject you must record the total number of classes timetabled for the semester – this will be used to calculate Student attendance percentage and Lecturer attendance percentage. An attendance fact must always contain a valid lecturer-id; we assume if one lecturer is absent then another will take his/her place.
You may assume maximum room usage is 11 X 5 = 55 periods a week.
You can work out the primary key of the fact table from the above specification.
The following criteria must be met:
1) This is an individual assignment.
2) You must write SQL statements to enter the data; Try not to repeatedly enter the same thing for one class.
3) You will need reports to provide:
a) % usage of rooms
b) % attendance of students
c) % attendance of lecturers
d) At least 2 other reports of interest
4) You must enter at least 30 fact records (this is a factless fact table); in all 10 students, 5 different lecturers, 3 different rooms, 2 subjects and 10 date/time slots as your initial prototype system.
5) (Optional). You must load your fact table from an operational database in batch mode – e.g. a file with values
St No Lect –Id Room Subj Date Time
1 1 1 2 20110718 1130
2 1 1 2 20110718 1130
6 1 1 2 20110718 1130

Or (1, 2, 6) 1 1 2 110718 1130 - much shorter etc. showing all students attending a given class
You will be evaluated to check system meets all practical requirements (works properly):
 Check star schema structure (7)
 Check fact table for suitable data (6)
 Check entry of data (14)
 Check reports (13)
…...

Similar Documents

Database

...Introduction $  $  • Purpose of Database Systems • Data Definition Language • Data Manipulation Language • Transaction Management & ' & • Storage Management • Database Administrator • Database Users • Overall System Structure 1.1 Silberschatz, Korth and Sudarshan c 1997 Database Systems Concepts Database Management System (DBMS) • Collection of interrelated data • Set of programs to access the data • DBMS contains information about a particular enterprise • DBMS provides an environment that it both convenient and efficient to use Database Systems Concepts 1.2 Silberschatz, Korth and Sudarshan c 1997 ' & ' & Purpose of Database Systems $  $  Database management systems were developed to handle the following difficulties of typical file-processing systems supported by conventional operating systems. • Data redundancy and inconsistency • Difficulty in accessing data • Data isolation – multiple files and formats • Integrity problems • Atomicity of updates • Concurrent access by multiple users • Security problems Database Systems Concepts 1.3 Silberschatz, Korth and Sudarshan c 1997 View of Data An architecture for a database system view level view 1 view 2 … view n logical level physical level Database Systems Concepts 1.4 Silberschatz, Korth and Sudarshan c 1997 ' & ' & Levels of Abstraction $  $  • Physical level: describes how a record (e.g., customer) is stored. • Logical level: describes data stored in database, and the relationships among......

Words: 1023 - Pages: 5

Database

...Information: processed data • • • • Database Metadata DBMS: Database management system Database system 1 An Example of Data • Sales per employee for each of Xcompany’s two divisions 2 Data and Its Structure • Data is actually stored as bits, but it is difficult to work with data at this level. • It is convenient to view data at different levels of abstraction. • Schema: Description of data at some level. Each level has its own schema. • Three schemas: physical, conceptual, and external. 3 Physical Data Schema • Describes details of how data is stored: tracks, cylinders, indices etc. • Early applications worked at this level - explicitly dealt with details. • Problem: Routines hard-coded to deal with physical representation. – Changes to data structure difficult to make. – Application code becomes complex since it must deal with details. – Rapid implementation of new features impossible. 4 Conceptual Data Level • Hides details. – In the relational model, the conceptual schema presents data as a set of tables. • DBMS maps from conceptual to physical schema automatically. • Physical schema can be changed without changing application: – DBMS must change mapping from conceptual to physical. • Referred to as physical data independence. 5 Conceptual Data Level (con’t) Application Conceptual view of data DBMS Physical view of data 6 External Data Level • In the relational model, the external schema also presents data as a......

Words: 1553 - Pages: 7

Star Schem & Snowflake Schema

...we will go through different Schema that can be used during Dimensional Modelling to create a Data Warehouse. Before we start with today's topic , For my viewers those who are new to this field i would like to revisit some of the key points of my previous blogs: 1) Business Intelligence is mainly divided into three parts as per my understanding a) Data Warehouse design and Implementation (ETL process) b) Data Analysis (Using OLAP cubes) c) Reporting and Dashboard Creation For further details revisit my First blog 2) Important Components involved in Dimensional Modelling or Data Warehouse Designing a) Fact Tables (Additive Facts, Semi-Additive Facts, Non- Additive Facts) b) Dimension Table c) Grain For further details revisit my Second blog After a thorough revision of previous concepts lets start our today's discussion about different Schema involved in Dimensional Modelling or Data Warehouse Designing. First of all i would like to explain the meaning of the topic i.e Snow Covered Wagon Hitched to a Star = SnowFlake Schema and Star Schema are two types of Schema that are used while designing a Data Warehouse, Hence they can be explained as follows: Star Schema: A Star Schema is one of the simplest and easiest schema to understand. A schema which consists of Dimension tables only attached to Fact tables. A Star Schema get its name from its......

Words: 557 - Pages: 3

Database

...meaning. Database - represents some aspect of the real world (miniworld or universe of discource – UoD). Collection of related data (a random assortment of data is not a database). A DB is designed, built, and populated with data for a specific purpose. It has an intended group of users and a some preconceived applications in which the users are interested. DBMS - is a collection of programs that enables users to create and maintain a database. database system – it is a combination of database and DBMS software. program – data independence – The structure of data files is stored in the DBMS catalog separately from the access programs. user view - – includes a number of external schemas that describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. DBA – is an IT professional that is responsible for Installation, Configuration, Upgrade, Administration, Monitoring, Maintenance and Securing of databases in an organization. The chief administrator of the database. end user - are the people whose jobs require access to the database for querying, updating, and generating reports; the database primarily exists for their use. data model - a collection of concepts that can be used to describe the structure of a database-provides the necessary means to achieve this abstraction. database schema - of a database system is its structure described in a formal language supported by the database......

Words: 959 - Pages: 4

Database

...Information Systems 3 (database 3) Star Schema Draft 1 You must create a Star Schema University Attendance database for the following situation: The database must record attendance of students in classes, the time of the lectur e, the lecturer who taught the class, the room that was used and the subject for that lecture. For each subject you must record the total number of classes timetabled for the semester – this will be used to calculate Student attendance percentage and Lectu rer attendance percentage. An attendance fact must always contain a valid lecturer - id; we assume if one lecturer is absent then another will take his/her place. You may assume maximum room usage is 11 X 5 = 55 periods a week. You can work out the primary k ey of the fact table from the above specification. The following criteria must be met: 1) This is an individual assignment. 2) You must write SQL statements to e nter the data ; Try not to repeatedly enter the same thing for one class. 3) You will need reports to provide: a) % usage of rooms b) % attendance of students c) % attendance of lecturers d) At least 2 other reports of interest 4) You must enter at least 3 0 fact records (this is a factless fact table); in all 10 students, 5 different lecturers, 3 different rooms , 2 subjects and 10 dat e/time slots as your initial prototype system. 5) (Optional ). You must load your fact table from an operational......

Words: 304 - Pages: 2

Star Schema

...Information Systems 3 You must create a Star Schema University Attendance database for the following situation: The database must record attendance of students in classes, the time of the lecture, the lecturer who taught the class, the room that was used and the subject for that lecture. For each subject you must record the total number of classes timetabled for the semester – this will be used to calculate Student attendance percentage and Lecturer attendance percentage. An attendance fact must always contain a valid lecturer-id; we assume if one lecturer is absent then another will take his/her place. You may assume maximum room usage is 11 X 5 = 55 periods a week. You can work out the primary key of the fact table from the above specification. The following criteria must be met: 1) This is an individual assignment. 2) You must write SQL statements to enter the data; Try not to repeatedly enter the same thing for one class. 3) You will need reports to provide: a) % usage of rooms b) % attendance of students c) % attendance of lecturers d) At least 2 other reports of interest 4) You must enter at least 30 fact records (this is a factless fact table); in all 10 students, 5 different lecturers, 3 different rooms, 2 subjects and 10 date/time slots as your initial prototype system. 5) (Optional). You must load your fact table from an operational database in batch mode – e.g. a file with values St No Lect –Id Room Subj Date Time 1 1 1 2 20110718 1130 2 1 1 2 20110718 1130 6 1...

Words: 292 - Pages: 2

Database

......,An be attribute names with associated domains D1, D2, . . . , Dn, then R(A1: D1,A2: D2,...,An: Dn) is a relation schema. For example, Student(StudId: integer, StudName: string, Major: string) • A relation schema specifies the name and the structure of the relation. • A collection of relation schemas is called a relational database schema. * 1. List the names of all students who have borrowed a book and who are CS majors. πStName(σSTUDENTS.StId=borrows.StId (σMajor=’CS’(STUDENTS)×borrows)) * 2. List the title of books written by the author ’Silberschatz’. πTitle(σAName=’Silberschatz’ (σhas-written.DocId=BOOKS.DocID(has-written×BOOKS))) or πTitle(σhas-written.DocId=BOOKS.DocID (σAName=’Silberschatz’(has-written)×BOOKS)) * 3. As 2., but not books that have the keyword ’database’. * . . . as for 2. . . . − πTitle(σdescribes.DocId=BOOKS.DocId (σKeyword=’database’(describes)×BOOKS)) * 4. Find the name of the youngest student. πStName(STUDENTS)− πS1.StName(σS1.Age>S2.Age(ρS1(STUDENTS)×ρS2(STUDENTS))) * 5. Find the title of the oldest book. πTitle(BOOKS)−πB1.Title(σB1.Year>B2.Year(ρB1(BOOKS)×ρB2(BOOKS))) * Relational algebra is a language that is being used to explain basic relational operations and principles. * It is procedural language which instructs the system to perform a specific sequence of tasks on the database. * Relational algebra is a procedural query language with five fundamental operations. These operations include......

Words: 700 - Pages: 3

Database

...chapter 2 Database System Concepts and Architecture he architecture of DBMS packages has evolved from the early monolithic systems, where the whole DBMS software package was one tightly integrated system, to the modern DBMS packages that are modular in design, with a client/server system architecture. This evolution mirrors the trends in computing, where large centralized mainframe computers are being replaced by hundreds of distributed workstations and personal computers connected via communications networks to various types of server machines—Web servers, database servers, file servers, application servers, and so on. In a basic client/server DBMS architecture, the system functionality is distributed between two types of modules.1 A client module is typically designed so that it will run on a user workstation or personal computer. Typically, application programs and user interfaces that access the database run in the client module. Hence, the client module handles user interaction and provides the user-friendly interfaces such as forms- or menu-based GUIs (graphical user interfaces). The other kind of module, called a server module, typically handles data storage, access, search, and other functions. We discuss client/server architectures in more detail in Section 2.5. First, we must study more basic concepts that will give us a better understanding of modern database architectures. In this chapter we present the terminology and basic concepts that will be used......

Words: 11529 - Pages: 47

Star Schema

...Information Systems 3 (database 3) Star Schema Draft 1 You must create a Star Schema University Attendance database for the following situation: The database must record attendance of students in classes, the time of the lecture, the lecturer who taught the class, the room that was used and the subject for that lecture. For each subject you must record the total number of classes timetabled for the semester – this will be used to calculate Student attendance percentage and Lecturer attendance percentage. An attendance fact must always contain a valid lecturer-id; we assume if one lecturer is absent then another will take his/her place. You may assume maximum room usage is 11 X 5 = 55 periods a week. You can work out the primary key of the fact table from the above specification. The following criteria must be met: 1) This is an individual assignment. 2) You must write SQL statements to enter the data; Try not to repeatedly enter the same thing for one class. 3) You will need reports to provide: a) % usage of rooms b) % attendance of students c) % attendance of lecturers d) At least 2 other reports of interest 4) You must enter at least 30 fact records (this is a factless fact table); in all 10 students, 5 different lecturers, 3 different rooms, 2 subjects and 10 date/time slots as your initial prototype system. 5) (Optional). You must load your fact table from an operational database in batch mode – e.g. a file with values St No Lect –Id Room Subj......

Words: 377 - Pages: 2

Database

...Case Study: Database Development ALEXANDRA DESINORD PROFESSOR OSAMA MORAD CIS 515- STRAT PLAN FOR DBASE SYSTEMS Week 7 Assignment February 29, 2016 The more companies base their large elements of their business strategy around data, the need for IT team and executives with deep understanding of data and analytics is growing rapidly. Data changes all of the time. That is why it is essential that companies should try to stay up to par with the ever changing technology so that they can ensure that the system can withstand anything that comes about. Down below I will discuss briefly the various aspects of the SLDC and the tasks that can be completed to help solve certain common problems that exist from within. Customer data management is not high on many organizations' lists of priorities until it has to be. The term used in systems engineering, information systems and software engineering to describe a process for planning, creating, testing, and deploying an information system is called Systems development life cycle (SDLC). This term is also referred to as the application development life-cycle, which is a term used in systems engineering, information systems and software engineering to describe a process for planning, creating, testing, and deploying an information system. The task of optimizing the software development process has faced many obstacles. The main obstacle is a lack of accurate and complete data on the development process. The use of the......

Words: 2352 - Pages: 10

Star Schema University Attendance Databse

...You must create a Star Schema University Attendance database for the following situation: The database must record attendance of students in classes, the time of the lecture, the lecturer who taught the class, the room that was used and the subject for that lecture. For each subject you must record the total number of classes timetabled for the semester – this will be used to calculate Student attendance percentage and Lecturer attendance percentage. An attendance fact must always contain a valid lecturer-id; we assume if one lecturer is absent then another will take his/her place. You may assume maximum room usage is 11 X 5 = 55 periods a week. You can work out the primary key of the fact table from the above specification. The following criteria must be met: 1) This is an individual assignment. 2) You must write SQL statements to enter the data; Try not to repeatedly enter the same thing for one class. 3) You will need reports to provide: a) % usage of rooms b) % attendance of students c) % attendance of lecturers d) At least 2 other reports of interest 4) You must enter at least 30 fact records (this is a factless fact table); in all 10 students, 5 different lecturers, 3 different rooms, 2 subjects and 10 date/time slots as your initial prototype system. 5) (Optional). You must load your fact table from an operational database in batch mode – e.g. a file with values St No Lect –Id Room Subj Date Time 1 1 1 2 20110718 1130 2 1 1 2 20110718 1130 6......

Words: 328 - Pages: 2

Star Schema University Attendance Database

...You must create a Star Schema University Attendance database for the following situation: The database must record attendance of students in classes, the time of the lecture, the lecturer who taught the class, the room that was used and the subject for that lecture. For each subject you must record the total number of classes timetabled for the semester – this will be used to calculate Student attendance percentage and Lecturer attendance percentage. An attendance fact must always contain a valid lecturer-id; we assume if one lecturer is absent then another will take his/her place. You may assume maximum room usage is 11 X 5 = 55 periods a week. You can work out the primary key of the fact table from the above specification. The following criteria must be met: 1) This is an individual assignment. 2) You must write SQL statements to enter the data; Try not to repeatedly enter the same thing for one class. 3) You will need reports to provide: a) % usage of rooms b) % attendance of students c) % attendance of lecturers d) At least 2 other reports of interest 4) You must enter at least 30 fact records (this is a factless fact table); in all 10 students, 5 different lecturers, 3 different rooms, 2 subjects and 10 date/time slots as your initial prototype system. 5) (Optional). You must load your fact table from an operational database in batch mode – e.g. a file with values St No Lect –Id Room Subj Date Time 1 1 1 2 20110718 1130 2 1 1 2 20110718 1130 6 1...

Words: 328 - Pages: 2

Star Schema

.......................................................................... 8.3 8.4 Assessment criteria ...................................................................................... 8.4 8.5 Self-study activities ....................................................................................... 8.5 ____________________________________________________________________________  2015 Cape Peninsula University of Technology, Informatics & Design Page 3 of 18 Information Systems 3b (ISY300B, ISY313B) Organisational component 1. Introduction / Word of welcome This subject will build on the basics of database modelling and development techniques acquired in the previous 2 years. Information Systems 3b also covers the development of databases for applications systems with an emphasis on problem solving. Advanced related theoretical concepts such as database implementation, testing and quality will also be addressed. This subject will require students to practice previously acquired programming, academic writing and communications skills. Although the emphasis during this year is to acquire, interpret and discuss new knowledge, the critical evaluation of IT problems, the practical application thereof and the development of practical solutions will be encouraged through the use of real life business entity. 2. General 2.1 Contact information Name Building and room number Telephone number E-mail address Consulting hours Subject Co-ordinator Mr......

Words: 3807 - Pages: 16

Databases

...Database and database components Database management system Database models Database languages Database warehouse Database dictionary and indexing Databases A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic قائمة مراجع, full-text, numeric, and images. Database components Tables, Columns and Rows These three items form the building blocks of a database. They store the data that we want to save in our database. * Character or information about the attributes of an entity is stored in fields that is, individual items of data that we wish to store (like customer’s name). and it is represented in a cell. Columns * All fields containing data about one entity form a record (like one customer). And it is represented by columns. Rows * All related records form a file (like the customer file). And it is represented by rows. In databases, a row can be made up of as many or as few columns as you want. This makes reading data much more efficient - you fetch what you want. Tables * A set of interrelated, centrally coordinated Files forms a database (like a customer database). And it is represented by tables that make up the entire database and it is important that we do not duplicate data at all, including both rows and columns. Keys * Keys are used to relate one table for another. For example, A......

Words: 3581 - Pages: 15

Star Transformation

...mentioned herein are the property of their respective owners. Contents Star Transformation in Siebel Analytics(OBIEE) (Subrata Dass) Introduction Star Transformation is a join method mostly used in Data Warehousing Environments to fine tune typical query performance. It is of utmost importance in DB environments where reporting tools like Siebel Analytics (OBIEE) is in use. It can also be used for other tools such as Business Objects. The conventional join mechanisms that the star transformation seeks to supersede are Hash join, nested-loops and sort merge join. This paper shows how to take full advantage of this RDBMS feature. Another myth it dispels is the fact that implementation of the Star Query is not possible without creating a physical primary key foreign key dependency between the fact and the dimension tables All the necessary database parameters and other settings required for the set up of this feature have been highlighted in this paper. Parts of this paper which include the understanding of Star Transformation concept have been influenced by the paper Supercharging Star Transformations by Jeff Maresh in particular and various other Oracle resources mentioned in the References section All the results in this paper were produced on Oracle 10.2.0.4 64 Bit . Dimension Schema Used The Schema used in this paper is same as the one shown above. It is a real life schema currently in use in a renowned pharmaceutical company. The following......

Words: 4912 - Pages: 20