New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

File Structures and Database Systems

by: Mrs. Lue Goyette

File Structures and Database Systems CPSC 332

Marketplace > California State University - Fullerton > ComputerScienence > CPSC 332 > File Structures and Database Systems
Mrs. Lue Goyette
Cal State Fullerton
GPA 3.84

Xiong Wang

Almost Ready


These notes were just uploaded, and will be ready to view shortly.

Purchase these notes here, or revisit this page.

Either way, we'll remind you when they're ready :)

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Xiong Wang
Class Notes
25 ?




Popular in Course

Popular in ComputerScienence

This 19 page Class Notes was uploaded by Mrs. Lue Goyette on Wednesday September 30, 2015. The Class Notes belongs to CPSC 332 at California State University - Fullerton taught by Xiong Wang in Fall. Since its upload, it has received 74 views. For similar materials see /class/217063/cpsc-332-california-state-university-fullerton in ComputerScienence at California State University - Fullerton.

Similar to CPSC 332 at Cal State Fullerton

Popular in ComputerScienence


Reviews for File Structures and Database Systems


Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 09/30/15
SecondaIy Storage Track Cylinder EmaInna D Wang Cal State Fullerton SecondaIy Storage Seek time readwrite head on track Rotational delay latency first sector block under head Block transfertime readwrite one block EmaInna D Wang Cal State Fullerton 2 An Example Seagate Cheetah Capacity 9 GB Average seek time 8 msec Spindle speed 10000 rpm Average rotational delay Half of one track gt 3 msec Transfer time per track Rotation time per trackgt6 msec Size of sectors 512 bytes Number of sectors per track 170 Number of tracks per cylinder 16 Number of cylinders 6526 Ema2mm D Wang Cal State Fullerton 3 Some Calculations Assume we have 34 000 records each has 256 bytes Contiguous storage Total number of tracks 2 recordsper sector gt 17000 sectors gt 100 tracks Total time for reading one track seek time rotational delay transfer time seek time 8 msec rotational delay 3 msec transfer time per track 6 msec Total time for reading one track 17 msec 8152006 Dr warm Cal State Fullenun Some Calculations Total time for reading one track 17 msec Total time for reading all records 17 x 100 17 sec Is this the best we can do No How about arranging the tracks to cylinders 100 tracks 6 cylinders 4 tracks Reading one cylinder seek time rotational delay transfer time for one cylinder 8 3 6x 16 107 msec Time for reading 6 cylinders 6 x107 642 msec Time for reading 4 tracks 8 3 6 x 4 35 msec Total time 642 35 677 msec 8152006 Dr warm Cal State Fullerlun Some Calculations Random storage Minimum reading unit gt cluster 8 sectors Transfer time per cluster 8 x 6170 gt 028 msec Total time for reading one record 8 3 028 1128 msec Total time for reading all records 34000x1128383520 msec 6 min 2352 sec Is this the reality No Two records in one sector Total of sectors is 17000 The worst case is 17000 X1128 191760 msec 3 min 1176 sec 8152006 Dr Wang Cal State Fullenun Basic File Operations Opening a le for readWrite In C include ltstdiohgt indude ltfstreamhgt Fquote fpi Two ways to open fp fopen lename type fstream fp lename iosin Type could be I read anritey an ap end fstream fp rquot open for both read and write fp39openmlenamei iosxin wquot create for both read and write aquot open or create for both read 35 CDUld be inquoty DUV39y aPPquoty and write tr nc and the bit OR combination of them BlBZEIEIB Dy wayyg Cal StaleFullenun Basic File Operations formatted input and output In C In C Int int i short short long k long k oat I oat I double a double a fscanffp d hd Id f If ampi fp gtgt i gtgtj gtgt k gtgt gtgt a ampj amp Be aware of the ampquot everywhere What a typica example of polymorphism BlBZEIEIB Dy Wang Cal StaleFullenun Basic File Operations inputoutput as a byte stream In C In C freadampbuff size number fp fpreadampbuff sizeof read reads from le fp to buffer buff reads 39om le fp to buffer buff size is the size of the data sizeof readquot is the number of type and number is the number ytes to be read regardless of of units to be read the data type eg eg int a10 int a10 freadampa sizeofint 10 fp fpreadampa 10sizeofint a array of 10 integers into Reads an array of10 integers into buff BlBZEIEIB Dy wayyg Cal StaleFullenun Basic File Operations inpquutth as a byte stream continue n C fwriteampbuff size number fp writes a certain number of units whose size Is sizequot fgetsampbuff BuffSize fp fputsampbuff fp Readswrites a string of Buffsize1 characters or until nquot is o ered in which case a nquotwi be the last character in buff Bl ZEIEIB In C fp writeampbuff sizeof write writes a sizeof write bytes from buff to le fp fpgetineampbuff BuffSize Deli Reads a line of BuffSize1 characters or until a delimiter Deli default is endquot is encountered Dy Wan y Cal StateFullenun Basic File Operations moving around in the le n C fseekfp offset origin moves the le pointer fp from origin by offset where offset is a long number of bytes and origin could be 0 beginning of the le 1 current position and 2 end of the le Bl ZEIEIB In C fpseekgoffset iosbeg fpseekpoffset iosbeg moves the readwrite e pointer Offset is the number of bytes to move the pointer ios could be beg beginning ofthe le cur current position and end end of the le Dy Wan y Cal StateFullenun Basic File Operations checking the status and other operations In C At opening iffpNULL Checking end of the le iff EOF for text le iffeoffp for binary le fcosefp closes the le and ushfp forces the operating system to write the buffer to the disk Bl ZEIEIB In C These functions will check the status of the e fpfai fpgoodo fpbad fpcose closes the le and fp ush forces the operating system to write the buffer to the disk Dy Wan y Cal StateFullenun SQLThe Structural Query Language 1 CR EATE DATABAS E CREATE DATABASE ltdbnamegt GRANT ALL ON ltdbnamegt TO ltusergtocalhost IDENTIFIED BY ltpasswordgt 2 CREATE TABLE CREATE TABLE lttabenamegt ltcolumngt ltdatatypegt ltattribute constraintgt ltcolumngt ltdatatypegt ltattribute constraintgt lttabe constraintgt lttabe constraintgt anmuus m Wane CEI 2mg Fuiie m SQLThe Structural Query Language Datatypes a CHARltsizegt size lt 255 bytes lt9 VARCHARltsizegt size lt 255 bytes 6 BLOB or TEXT size lt 65535 9 MEDIUMBLOB or MEDIUMTEXT size lt16777215 9 LONGBLOB or LONGTEXT size lt 4 GigaBytes Q ENUMltvaIue1gt ltvaue2gtltvauen O TINYINT SMALLINT MEDIUMINT INT BIGINT are integers of1 23 4 ytes r s e 39 DEOIMAL or NUMERCM D 000 FLOAT DOUBLE PRECISION DATE default format YYYYMMDD eg 19971004quot anmuus m WarIg CEI 2mg Fuiie m SQLThe Structural Query Language Attribute constraints 9 N OT N U LL 4 UNIQUE Q PRIMARY KEY DEFAULT ltVauegt Table constraints CONSTRAINT ltnamegt o PRIMARY KEY ltattributegt ltattributegt O FOREIGN KEY ltattributegt REFERENCES lttabegtltattributegt ON DELETE SET NULL ON DELETE CASCADE 9 UNIQUE ltattributegt ltattributegt anmuus m Wang CEI 2mg FuIIenm SQLThe Structural Query Language CREATE TABLE STU DENTSSN numeric9 primary key F E varchar20 LNAME varchar20 SEX enum M F DBIRTH date STADDRESS varchar20 MAJOR char4 CLASS tinyint anmuus Dr Wane Cal 2mg Fuiie m SQLThe Structural Query Language CREATE TABLE cou Rs ECNU M char7 primary key CNAME varchar30 TEXTBOOK varchar50 UNITS tinyint DEPARTMENT varchar30 CREATE TABLE ENROLLSNO numeric9 CNO char7 GRADE enum A B C D F W primary key SNO CNO foreign key SN 0 references STU D ENTSSN foreign key CN 0 references COU RSECN U M anmuus Dr Wang Cal 2mg Fuiie m SQLThe Structural Query Language 3 DROP DROP DATABASE ltdbnamegt DROP TABLE lttabenamegt anmuus Dr Wang Cal 2mg Fuiienm SQLThe Structural Query Language 4 ALTER TABLE ALTER TABLE ltnamegt ADD ltcolumngt ltdatatypegt ltcoumngt ltdatatypegt ALTER TABLE ltnamegt MODIFY ltcolumngt ltdatatypegt ltcoumngt ltdatatypegt ALTER TABLE ltnamegt MODIFY ltcolumngt PRIMARY ALTER TABLE ltnamegt MODIFY ltcolumngt UNIQUE ALTER TABLE ltnamegt MODIFY ltcolumngt DEFAULT ltvauegt ALTER TABLE ltnamegt RENAME TO ltnevmamegt ALTER TABLE ltnamegt DROP ltcolumngt ltcolumngt ALTER TABLE ltnamegt DROP PRIMARY ltcolumngt ltcoumngt ALTER TABLE ltnamegt DROP UNIQUE ltcolumngt ltcoumngt ALTER TABLE ltnamegt DROP CONSTRAINT ltnamegt anmuus Dr Wang CaI State FuIIe an SQLThe Structural Query Language 5 INSERTtupIes INSERT INTO lttabegt VALUES ltvaue1gt ltvauengt ltvaue1gt ltvauengt are he values for the attributes in exactly the e orderwhen the table was created You can change the order using the following statement INSERT INTO lttabegtltattn39bute1gt ltattribute2gt VALUESltvaIue1gtltvaue2gt anmuus Dr Wang CaI State FuIIe an SQLThe Structural Query Language 6 U PDATE UPDATE lttabegt SET ltattributegtltvauegt WHERE ltconditionsgt 7 DELETE DELETE FROM lttabegt WHERE ltconditionsgt anmuus Dr Wang CaI State FuIIenan CPSC 332 Part 1 Database Systems Xiong Wang PhD Associate Professor Telephone 7142787258 Email xwangfuertonedu What is a database system Usmcmummm v Apmnam mewuwm mans 3 mm a uhuncswmgmm an mums m Wang Cat 21312 FuHenan Component Modules of a DBMS w a mansion s tadv an mums an A physical centralized architecture Figurn 24 A physxcal cenhallreda acme HardwareFirmware A clientserver architecture F39Qme 23 Diskless Client Server PhySIczl two39 ev r with Disk Server and cum Ien tserver rchvteclure Site 2 E mu Dv Wm cal State mm 5 The hierarchical model ammus Dr Wang Cal 21mg Fulle an 5 The network model amimus m Wang Cal State muenan The relational model amimus m Wang Cal State muenan Brief history of the relational model 1970 EF Codd A Relational Model For Large Shared Data Banksquot CACM 1973 1980 Mike Stonebraker developed INGRES 1980 Stonebraker founded INGRES Corp 1981 E F Codd received Turing Award 1983 Codd s paper of 1970 was reprinted in CACM 1986 Codd speci ed 13 criteria for a DBMS to be a relational DBMS 1994 INGRES was purchased by Computer Associates 1994 Stonebraker started POSTGRES incorporating object relational concept amimus m Wang Cal State Fulle an Brief history of the ER Model 1975 Peter P Chen The Entity Relationship Model Toward a Uni ed View of Dataquot VLDB 1975 and TODS 1976 1979 First ER conference was held 23 in 2004 1999 Peter P Chen ER Model XML and the Webquot In 2003 Dr Chen received the IEEE Harry Goode Award and the ACMAAAI Allen Newell Award In June and July 2006 the TODS 1976 paper ranked 10th among the top 10 downloads 39om ACM s Digital Library It also ranked 9m among the top downloads in the year 2006 up to July 2006 ammus m Wang cm State FuHenan m The fate of XML and SQL Don Chamberlin IBM 1 think that XML will become the dominant format for data interchangequot with its flexibility and ability to provide self description Relational databases will be fitted with front ends to support XML and process queries based on the XQuery standard SQL will not go away but there are new data formats for which it just was not designed ammus m Wangca1me FuHenan 11 The fate of XML and SQL Rick Cattell SUN quot1 think the momentum behind relational databases is insurmountablequot very few people are going to store XQuery data in an XML format Developers will need tools to convert relational data to XML and vice versa ammus m Wang cm State Fulle an 2 The fate of XML and SQL Daniela Florescu CTO at Xer Documents will be stored nativer in XML Xer offers a version of the XQuery XML query language Eventually an extension of XQuery will replace both Java and SQL Cattell quotI don39t think XQuery is ever going to replace SQL or Javaquot but there may be a query language to replace SQL amimus m Wang Cal State muenan 13 The fate of XML and SQL Jim Gray Microsoft The real challenge we face is to make computers self managing so the management cost is less than the capital cost quotThe problem with p to p computing for databases is you have to send a lot of data around and 1 will buy you a lot of computingquot amimus m Wang Cal State muenan m Are we reaching the end of SQL39s life Jeffery Ullman Stanford No the spirit will remain alive SQL will adapt There39s been a lot of research into semistructu red data of which XML isjust an example People have just begun to scratch the surface of how you optimize SQLlike queries on XML ortreeIike structures This is a very exciting area for the future amimus m Wang Cal State Fulle an 15 Are we reaching the end of SQL39s life Stephen Brobst NCR With more and more analytics and other work coming into the database see SQL remaining very much alive Advanced analytic functions like data mining are going to move into SQL There39s just a ton of stuff that39s going to happen with SQL going forward The nice thing about SQL is that it39s a functional language and therefore has very desirable properties for parallelization amimus m Wang Cal State Fullenan 15 Are we reaching the end of SQL39s life Ken Jacobs Oracle SQL will be revitalized by these trends and continued expansion not only to handle multimedia data and analytics but also to express business functions We have only begun to scratch the surface of exploiting this integration We39ll be able to do data mining or OLAP on collections of documents and then drive the results back into an operational OLTP environment amimus m Wang Cal State Fullenan 7 What is next for databases Ken Jacobs Oracle Certainly the integration of SQL and XML and having a single repository that can store both relational and XML data will give businesses a great deal of power You will be able to do data mining or other B activities on XML data Of course this integration is going to be critical to Web services amimus m Wang Cal State Fulle an a MySQL Introduction Dr Xiong Wang Department of Computer Science Cal State Fullerton MySQL Connect mysql h host u user ppassword dbname gtmysql u wang p Enter password Welcome to the MySQL monitor Commands end with or g Your MySQL connection id is 14 to server version 32334a pe 39help39 or 39h39 for help Type 39c39 to clear t e buffer mys qlgt MySQL Data definition CREATEDROP DATABASE dbname SHOW DATABASES USE dbname CREATE TABLE tablename fieldname type constraints SHOW TABLES SHOW COLUMNS FROM tabename DROP TABLE tablename MySQL Data manipulation INSERT INTO tablename fieldname VALUES value DELETE FROM tablename WHERE condition UPDATE tablename SET fieldnamevalue WHERE condition MySQL Data manipulation2 SELECT fieldname as fieldname FROM tablename WHERE condition ORDER BY fieldname lt gt AND OR NOT fieldname LIKE quot MySQL ALTER TABLE ALTER TABLE tablename RENAME newtablenamel ADD fieldname type DROP fieldname CHANGE name newname newtype mysqlgt show databases Databas e 2 rows in set 000 sec mysqlgt use test Database changed mysqlgt create database test2 Query OK 1 row affected 000 sec mysqlgt CREATE TABLE Students gt Sid VARCHAR8 NOT NULL gt sname VARCHAR20 gt snum INT gt PRIMARY KEYsid Query OK 0 rows affected 001 sec IData Type ICHAR VARCHAR INT Unsigned FLOAT IDATE TEXTBLOB SET ENU39M IConstraints IPRIMARYFOREIGN KEY NOT NULL mysqlgt show tables Tablesintest students 1 row in set 000 see mysqlgt show columns from students Field Type Null Key Default Extra sid varchar8 PRI sna archar20 YES NULL snum int11 YES NULL 3 rows in set 001 see mysqlgt INSERT INTO Students gt VALUES 412239 John Q39 2001 Query OK 1 row affected 000 sec mysqlgt SELECT FROM Students mysqlgt DELETE FROM Students gt WHERE sid 412239 39 Query OK 1 row affected 000 sec mysqlgt UPDATE Students gt SET sid 351539 gt WHERE sid 431139 Query OK 1 row affected 000 sec mysqlgt SELECT FROM Students sLd sname snum 4311 2002 5281 Ben Patterson 2000 8762 Peter Dawson 2000 3 row in set 000 sec mysqlgt SELECT FROM Students ORDER BY sname sLd sname snum 5281 Ben Patterson 2000 4311 ary Chau 2002 8762 Peter Dawson 2000 3 rows in set 002 sec mysqlgt SELECT sname FROM Students WHERE gtsnum2000 2 row in set 000 sec MySQLSHOW SHOW DATABASES LIKE XXX SHOW TABLES FROM dbname LIKE XXX SHOW COLUMNS FROM tblname FROM dbname LIKE XXX SHOW DATABASES SHOW TABLES FROM db SHOW COLUMNS FROMth FROM db MySQLUSE USE dbname USE db MySQLINSERT INSERT INTO tblname col1col2 VALUESdata1data2 INSERT INTOth nameage VALUES abaw 19 MySQLSELECT SELECT co1col2 SELECT FROM FROM tbname tblname WHERE XXX ORDER SELECT FROM BY XXX LIMIT XTX tblname WHERE name abaw SELECT nameage FROM tblname ORDER BY age SELECT FROM tblname LIMIT 5 MySQLUPDATE UPDATE tblname SET UPDATE tblname SET co1XXXco2XXX age19 WHERE XXX UPDATE tblname SET age19 WHERE name abaw MySQLDELETE DELETE FROM DELETE FROMth tblname WHERE XXX WHERE id abaW


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Kyle Maynard Purdue

"When you're taking detailed notes and trying to help everyone else out in the class, it really helps you learn and understand the I made $280 on my first study guide!"

Steve Martinelli UC Los Angeles

"There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."


"Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.